protecting sheets with macros using vba

Hi all,
I have a workbook with about 50 vba macros in it, and I need it protected from accidental overwrites of formulas.
I found the code to do this, and it can be seen in the code lines below:

ActiveSheet.Unprotect Password:="****"
ActiveSheet.Protect Password:="****", DrawingObjects:=True, Contents:=True, Scenarios:=True

This works beautifully - it has locked all cells I wanted, runs the macro, is colleague-proof...

Unfortunately it is also now me-proof.
I now want to edit some of the locked cells. When I unprotect the sheet manually I can select one spot, then I need to unprotect the sheet again to make a single change. Then unlock again to fill down... it wants to stay locked.

Is there a way to attach code to a single macro button (or other method) to unlock all cells and leave them unlocked until I make the changes, then relock upon running a macro through another button?

I don't want to have to remove all the lock / unlock coding in all the macros, make the changes, then put them back.

All advice gratefully received.

Sorry it has taken me so long to get back to you - I have had to cut the file down to get it under the limit (even when zipped).

the lock / unlock code is shown here, and is in every macro.

Please Login or Register  to view this content.

regards

I have a module named 'dev code' that contains the following:

Please Login or Register  to view this content.

The problem is with the code in worksheetmodule sheet1(SUMMATIVE)
this is an event macro that is handeled each time you move your cursor. that is causing the locks

Please Login or Register  to view this content.

the redlines need to be removed from this macro to stop this problem.

Then I recommend 2 changes.
1. remove al the protect and unprotect actions from your macro's and create to separate macro's to protect and unproctect the activesheet
2. In the protect code also include the parameter UserInterfaceOnly:=True what this does is only disallow users to change stuff but macros can run uninterupted without unprotecting them all the time

By having separate protect and unprotect macro's you can call on them whenever you want. I would not use a button because any body can press that..

the 2 macro's are very simple and you put them in a Module f.e. Module1 (which is now empty in your sheet)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Now when you have this you can remove all the unprotect and protect lines from your macro's
check to make sure your sheets are locked before you remove it.
the other advantage is that if some sneeky b*sterd discovers your passowrd you only have to change it in one place instead of all macro's

If you want to be really "fail save" put in each sheet codemodule this event macro

Please Login or Register  to view this content.

This then calls the protect macro so then you are sure the sheets are always locked when ppl activate them.

For your easy use of protecting and unprotecting you could assign shortcut keys to AS_Protect (suggestion CRTL+SHIFT+P) and AS_Unprotect (suggestion CRTL+SHIFT+U)
this way you can protect and unprotect whenever you want with a few keystrokes
and with the failsave of locking put in on activation it does not matter much if you forget to put it on.

So bit of a long story.. but these changes may help you manage protection more easy.

Thank you so much. Works perfectly.

thank you for the assist

working not so perfectly it seems. macro buttons and line colour macro are still not working when sheet is locked. I have added the macros as suggested.

Hi Mumps1,

partial success. The code in sheets 1 and 4 (highlighting rows) will still not work. Similarly, the tabs buttons do change colour successfully but refuse to show/hide the columns.
I appreciate your assistance
regards
Warwick

Thank you very much. It is working well.