You need to run an un-lock around your code.
Try using the code below.
put form buttons on the sheet to lock and unlock, the password is hardcoded into the VBA code and hidden. This way the user cannot change the password.
This code gos on the sheet module:
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
This code gos on the module:
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
This is a very simple form of this.
If this is the route you want to try, I have some very complex systems?
Note: The password is hard-coded.
The unprotect will automatically be re-protected by the first macro when the unprotect is done. JSW