MrExcel Publishing
Your One Stop for Excel Tips & Solutions

cpod, more help please, protect all worksheets


Posted by Paul on December 21, 2000 4:00 PM

I have no experience in VBA so please explain in detail.
I want to have a button on one sheet that will protect all the worksheets, in the book and one that will unprotect all the sheets, I want the user to have to put in a password, when the button to unprotect the sheets is pushed. Lets say the worksheets names are A,B, and C, and the password is p123. Thanks for your help.


Posted by cpod on December 22, 2000 5:05 AM


First, put in the two buttons. Open the VB toolbar:

View -> Toolbars -> Visual Basic

On the VB toolbar open the tools toolbar (hammer and wrench button) and click on the "Command Button" button. Click on the spreadsheet to add the button. Repeat for second button.

Change the button caption: Right click on the button and go to properties. Change the "Caption" to whatever to want to indicate protect. Click on the other button and change it's caption to unprotect. Close the properties list.

Now enter the code behind the buttons. Double click on the protect button. Enter this code between the "Private" and "end sub" statements:

Dim WS
Dim strPassWord As String
For Each WS In Worksheets
WS.Protect "enter the password here"
Next

In the line:
WS.Protect "enter the password here"

change: enter the password here

to whatever your password will be (Leave in the quotes)

Repeat for the second button and enter the code:

Dim WS
Dim strPassWord As String
strPassWord = InputBox("Please enter password")
If strPassWord = "enter the password here" Then
For Each WS In Worksheets
WS.Unprotect strPassWord
Next
Else
MsgBox ("The Password you have entered is incorrect")
End If


Change the password in the line:

If strPassWord = "enter the password here" Then


Close the VB editor. To test, click on the Design Mode button on the VB toolbar (blue protractor and ruler) so that it is not "depressed". The buttons should now be functional.