Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-27-2021, 06:59 PM
Marcia's Avatar
Marcia Marcia is offline Message to Protect Sheet after a command Windows 10 Message to Protect Sheet after a command Office 2019
Expert
Message to Protect Sheet after a command
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default Message to Protect Sheet after a command

Hi. The attached sheet is protected but the users may insert rows as needed within the form, and hide columns C D and E. But in order to do these tasks, the users must unprotect the sheet first. I would like help in a code that when the user unprotects the sheet, a message pops out like "protect the sheet after the insert rows or hide columns".
My search did not yield any satisfactory result.
Thank you.
Attached Files
File Type: xlsx ProtectMessage.xlsx (45.6 KB, 8 views)
Reply With Quote
  #2  
Old 09-28-2021, 07:19 AM
Logit Logit is offline Message to Protect Sheet after a command Windows 10 Message to Protect Sheet after a command Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

Rather than use a message to the User ... how about placing the required code in the ThisWorkbook module BEFORECLOSE ...
Reply With Quote
  #3  
Old 09-28-2021, 07:09 PM
Marcia's Avatar
Marcia Marcia is offline Message to Protect Sheet after a command Windows 10 Message to Protect Sheet after a command Office 2019
Expert
Message to Protect Sheet after a command
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Quote:
Originally Posted by Logit View Post
Rather than use a message to the User ... how about placing the required code in the ThisWorkbook module BEFORECLOSE ...
Thank you Logit. My knowledge of vba is a big appreciation of what it does, copying the code supplied by generous experts in the internet that exactly fit my requirements. I ask help in this forum when I could not find the correct code that I need.
The BeforeClose code is for saving. I do not honestly know how to edit the code to protect the sheet after the user has executed the insert rows or hide commands.
Reply With Quote
  #4  
Old 09-28-2021, 07:15 PM
Logit Logit is offline Message to Protect Sheet after a command Windows 10 Message to Protect Sheet after a command Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

Ok ... well here is where you can learn a simple and small effort at VBA coding.

Google : Password Protect Sheet. Also Google : Auto-Saving Without Notification

Both of those will give you a myriad of websites to learn from. YouTube has plenty of videos as well. My preference is not YouTube ... guess I don't have the patience to sit through a short movie.

Let us know how you progress.
Reply With Quote
  #5  
Old 09-29-2021, 12:15 PM
Purfleet Purfleet is offline Message to Protect Sheet after a command Windows 10 Message to Protect Sheet after a command Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

as logit has said the beforeClose thisworkbook is ideal for what you want, but you dont need to ask the user to do anything.

If you open the this workbook Module in the VBA editor you will see drop downs at the top the one on the left (general) can be changed to Workbook (it acts when the workbook does something).

The one on the right is the thing that happens - there is quite a few, but some are really obvious like BeforeClose.

Select BeforeClose and a automated Private Sub will open called Workbook_BeforeClose

In between the 2 lines put in anythng you want to happen before the workbook is closed, for example

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

    ActiveWorkbook.Save
    Worksheets("Form").Protect
    
End Sub
Be careful with the Activeworkbook.save though because if the user does something stupid like delete all the data and then they close it because they panic it will save with all the data lost
Reply With Quote
  #6  
Old 09-30-2021, 12:40 AM
Marcia's Avatar
Marcia Marcia is offline Message to Protect Sheet after a command Windows 10 Message to Protect Sheet after a command Office 2019
Expert
Message to Protect Sheet after a command
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Thank you Logit and Purfleet. I have started reading on the link provided by Logit then I will proceed with Purfleet's code.
Reply With Quote
  #7  
Old 09-30-2021, 06:48 AM
Logit Logit is offline Message to Protect Sheet after a command Windows 10 Message to Protect Sheet after a command Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

Let us know how you progress.
Reply With Quote
  #8  
Old 09-30-2021, 08:07 AM
p45cal's Avatar
p45cal p45cal is offline Message to Protect Sheet after a command Windows 10 Message to Protect Sheet after a command Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by Purfleet View Post
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

    ActiveWorkbook.Save
    Worksheets("Form").Protect
    
 End Sub
If you save before closing, then protect the sheet, I suspect the workbook will ask again if it should be saved to which you have to answer Yes in order for the sheet to be protected in the saved file.
Personally, as your codicil suggests being wary of .Save, I'd miss it out altogether in that event handler and only include the .Protect line. That way, whether the file is saved or not (with/without erroneous changes made by the user) is left for the user to decide, which is the usual behaviour on closing a workbook.
Reply With Quote
  #9  
Old 09-30-2021, 08:37 AM
Logit Logit is offline Message to Protect Sheet after a command Windows 10 Message to Protect Sheet after a command Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

This macro, placed in the ThisWorkbook module, will save the workbook without notifying the user :

Code:
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Application.DisplayAlerts = False
    ThisWorkbook.Save
   
    Application.DisplayAlerts = True
    Application.Quit

End Sub
Reply With Quote
  #10  
Old 09-30-2021, 08:40 AM
Purfleet Purfleet is offline Message to Protect Sheet after a command Windows 10 Message to Protect Sheet after a command Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

It seemed to do both when i checked it, but fair point on the order.

I agree i would leave it out on the basis that IF a user can do somthing wrong they WILL do something wrong - and 99% of users will use the close excel as the get out!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Command button. To multi command Taylormations Word VBA 11 09-14-2020 02:39 AM
How do I un-protect docs I didn't protect? doctorduncan Word 4 05-30-2019 11:09 AM
Message to Protect Sheet after a command Outlook 2013 > Command to view the header without opening a suspicious message? Macintosh Outlook 2 02-04-2019 05:38 PM
Show table list by using chek box command and save document on sharepoint by using command button Zhibek Word VBA 3 08-16-2018 06:19 PM
Repair command results in error message srdiamond Office 0 03-01-2011 12:13 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:16 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft