Protection

Password lock sheet

Note: This will only lock the whole sheet or lock, all the lock selected cells on a sheet. The trick is to un-check the "Lock cells" box for the cells you don't want the code to password lock.
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

Hope this helps, JSW.

How do you use ActiveSheet.Unprotect with an actual password?

Code to unprotect sheet with no prompt.

Sub myUnLock()
'This code will unprotect the sheet.
Application.EnableEvents = False
Application.DisplayAlerts = False
ActiveSheet.Unprotect ("admin")
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub

Note: This is set to the Tab code password "admin."
The DisplayAlerts pair supresses Excel prompts.
You can run this from a "Hot-key" or a Form button in the Macro - Macros section. JSW Note: This will only lock the whole sheet or lock, all the lock selected cells on a sheet. The trick is to un-check the "Lock cells" box for the cells you don't want the code to password lock. : Then Right click the sheet tab where the data is. Select "View Code" and paste the code below, do not change the name or the code will fail! : Note the password now is: admin : When the cells that have been locked with Format - Cells - Protection checked "Lock;" on your sheet, then the code below will password locked them from changes. If you try to change cells, Excel will prompt you.

If you run the "unprotect" code the sheet will be unprotected and you will not be prompted that this has been done! : Note: You can change the code password to any you want, make a file backup incase you forget your password. : I tested this and it works. : To unlock the sheet: Tools - Protection - UnProtect Sheet then enter your password. : Macro by Joseph S. Was

Note: If you password lock with Tab Sheet code, Unprotect for each edit.

Note: If you paswword lock with Sheet Tab View Code, The unprotect will need to be run for each active cell. That is select cell press hot key for unprotect, edit. The next cell you go to will be protected, you must re-run the unprotect code for each active cell edit.

You can avoid this by converting the password code to a module level macro (Macro - Macros [{Name}Create].

As such the unprotect code is best run from a "Hot-key" if the password code is in the Sheet Tab View Code section! JSW Sub myUnLock() This code will unprotect the sheet. Macro by Joseph S. Was