If a range is in or part of a protected region, macro code will fail. To run a macro with protected ranges you must wrap your macro code in unprotect-protect code. Like:
In the sheet module put:
Private Sub Worksheet_Change(ByVal Target As Range)
'
' Macro by Joseph S. Was
'
Application.EnableEvents = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="admin"
Application.EnableEvents = True
End Sub
Then in a module add the unlock code and attach your code, if any, before the End Sub.
Note: You can run the un-lock code from a Form-Button or use a hot-key with Macros-Macro-select macro-Options-select your key.
Sub myUnLock()
'This code will unprotect the sheet.
Application.EnableEvents = False
Application.DisplayAlerts = False
ActiveSheet.Unprotect ("admin")
'Add optional code here!
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub
Then run your code out of the unlock sub, the first macro will password lock the code behind you automatically. You can make the password anything you want. To help hide your password you can password lock your macros form the VBA editor pull-down menu!
Note: this uses hard coded password protection which will decrease the security of the protection. But then again a password does not really deter someone from getting in if they want to, like a door lock does not stop some one from getting in a window, but does stop the causal passer-by from coming in.
To add a "MsgBox" to your macro and not use the code above, that is you do not want the user to run your macro through your button when the sheet is protected, you have two main ways of doing this:
You can add an: "On Error GoTo myErr" flag at the start of your code and at the bottom of your code put:
End
myErr:
MsgBox "This button has been deactivated!"
End Sub
Or record a two macros: One you right-click delete your form button off the sheet. Then add that code to the bottom of your protect code. Then record another macro, replacing your form button. Add that code to the begining of your unprotect macro.
In this way when you run the code to umprotect your macro button will be created on your sheet. And, when you run your protect code the button will be removed from your sheet. JSW