 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / August 2007Protection Reminder
I have used a combination of locking certain cells and then protecting worksheet in order to prevent accidentally overwriting cells with formulae in them. This works fine for what I want but occasionally I need to change a formula so have to unprotect the sheet. Again, no problem but what I am looking for is a reminder to reprotect the worksheet after I have amended the formulae I need to change. I guess there is a macro or something I could write but please bear with me since I am unfamiliar with writing macros. Also, is it possible for this to be aplied to all sheets in the workbook? Colin, You could try this:- Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim sht As Worksheet For Each sht In Sheets sht.Select If ActiveSheet.ProtectContents = False Then MsgBox (sht.Name & " isn't protected") End If Next sht End Sub Alt+F11 to open VB editor. Double click 'This workbook. On the right hand side from the dropdown select 'workbook'. In the other dropdown select 'before save and paste this code in. Was that any use? Mike > I have used a combination of locking certain cells and then protecting > worksheet in order to prevent accidentally overwriting cells with formulae in [quoted text clipped - 4 lines] > write but please bear with me since I am unfamiliar with writing macros. > Also, is it possible for this to be aplied to all sheets in the workbook? Unprotect ur sheet Start Macro recorder Protect ur sheet Stop recorder now u got a code to protect ur sheet There are difrent way's to start that code eg. Everytime u save ur sheet When u select another sheet When u select another cell When u change a cell When u open ur sheet and some others too whitch one do u vant ? "Colin G" skrev: > I have used a combination of locking certain cells and then protecting > worksheet in order to prevent accidentally overwriting cells with formulae in [quoted text clipped - 4 lines] > write but please bear with me since I am unfamiliar with writing macros. > Also, is it possible for this to be aplied to all sheets in the workbook? Thank you both. Not sure I fully understand the detail Mike but will give it a try. Excellent, I am probably looking to do it everytime I save sheet or workbook. It is really as a reminder if I forget to re-protect the sheet having made amendments. The macro will loop through every sheet and if it isn't protected you will get a message pop up. Mike > Thank you both. Not sure I fully understand the detail Mike but will give it > a try. Excellent, I am probably looking to do it everytime I save sheet or > workbook. It is really as a reminder if I forget to re-protect the sheet > having made amendments. ok i look like Mike allready did the job :-) "Colin G" skrev: > Thank you both. Not sure I fully understand the detail Mike but will give it > a try. Excellent, I am probably looking to do it everytime I save sheet or > workbook. It is really as a reminder if I forget to re-protect the sheet > having made amendments. Thanks again, will give it a go I have opened VB editor and have copied macro in but when I close spreadsheet it says there is a syntax error. It appears to be pointing to the second row (Dim sht ....) but not sure. Because I have no idea on how VB works I don't know where to start looking in order to correct whatever I have done wrong. Any further help would be appreciated The question might be out of date still i give it a shot : Looks like your still in the developing phase... I suggest : unprotect all sheets and/or workbook in One click Work is done ? protect your sheets and/or workbook Check the Add-in James 2007 available @ www.allocator.nl your welcome |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.