MrExcel Publishing
Your One Stop for Excel Tips & Solutions

protect/ password issue

Posted by Nancy on January 11, 2002 1:58 PM

I am trying to protect my worksheet with the protect option in Excel. Yet, if somebody else wants to unprotect it, they just have to go to the unprotect option. How can I protect the worksheet with a password? I mean, a password option comes up, but if you want to unprotect the worksheet, you don't need the password. Is that a flaw in Excel or am I just not getting it? I even wrote my own password macro and assigned it to a button but it gets overruled. Does anybody out there know what I am trying to say? And if so, do you have an answer? Thank you already! Nancy

Posted by Mudface on January 11, 2002 2:04 PM

Maybe I've misunderstood, but you just put in a password at the prompt.

Posted by Joe Was on January 11, 2002 2:15 PM

My system works, that is you cannot get around the password, but Excel has these limits:

You can save changes to a password-protected workbook without using the password by first opening the workbook as read-only. Make the changes you want in the workbook, and then save it with a different name. The workbook saved with a new name does not require a password and is available for editing.

If you add a password-protected workbook to a binder, the password protection is lost. You will be prompted to enter the password when you add the workbook to the binder, but the protection is removed after it becomes a binder section.

Other than the above I do not know what to tell you other than your workbook should password lock. There are two types of password protection in Excel one is the whole workbook is protected, that is you can't even see it without the password. And, Read-Only password protected, that is you can pull it up look at it and only make changes to a renamed copy.

Try this lock code at 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 is the unlock code it is in a module level module:

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