#1
|
||||
|
||||
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. |
#2
|
|||
|
|||
Rather than use a message to the User ... how about placing the required code in the ThisWorkbook module BEFORECLOSE ...
|
#3
|
||||
|
||||
Quote:
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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 |
#6
|
||||
|
||||
Thank you Logit and Purfleet. I have started reading on the link provided by Logit then I will proceed with Purfleet's code.
|
#7
|
|||
|
|||
Let us know how you progress.
|
#8
|
||||
|
||||
Quote:
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. |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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! |
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 |
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 |