MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Saving changes to workbook with password

Posted by Wiggy on July 05, 2001 5:41 AM

I have a project in which the code is protected with a password.
I would like to allow the user, to edit one of the databases within the workbook, then to be able to save these changes without having to access the password.
i.e. I would like a macro to remove the password, save the file, then re-establish the password.

Can it be done?

If not, any other suggestions?

Thanks for any advice given;

Posted by Joe Was on July 05, 2001 1:11 PM

Yes, it is easy to do what you want!
Here is a sample set of code:

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. 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.

Note: You can change the code password to any you want, make a file backup just encase you forget your password.

I tested both codes and they work.

To unlock the sheet manually: Tools - Protection - UnProtect Sheet then enter your password.

Note the password now is: admin

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

Note: This is set to the Sheet-Tab View-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.

Note: If you password 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. If the next cell you go to is set with Lock it will be protected, you must re-run the unprotect code for each locked active cell edit.

You can avoid this by converting the password code to a module level macro (Macro - Macros [{Name}Create]. Then the Protect - Un-protect will work sheet wide?

As such the unprotect code is best run from a "Hot-key" if the password code is in the Sheet Tab, View Code section or not? JSW

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

Hope this helps, JSW.