Excel Password Protection with VBA and Manual UnProtect, or vice-versa

Question

Excel Password Protection with VBA and Manual UnProtect, or vice-versa

Anonymous

I am a relative beginner in using VBA. So, my apologies if this should be inherently obvious.

I have a workbook of 9 spreadsheets that i am trying to keep protected. While executing my VBA code i will unprotect a sheet using **sheetx.unprotect password = "pword".**Then, use Sheetx.Protect password = "pword" to protect it again.

However, there are times (esp during troubleshooting) when i need to manually Protect, or Un-Protect, a sheet. I noticed that if I use the above command to protect a sheet, that i cannot manually Un-Protect that sheet by clicking on the Un-Protect Button and entering the password - "pword".

The same thing happens in reverse, if I manually protect a sheet, I can't un-protect it using the sheetx.unprotect command with the same password.

Am I doing something wrong? Are the sheet.protect and sheet.unprotect VBA commands not the same thing as the Protect and Un-Protect buttons? I can't find anything that addresses this on-line.

I'm using Version 16.0.6868.1016

Thanks,

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

HansV 462.7K MVP Volunteer Moderator

Oh wait, I should have seen this. You should use

... Password:="pword"

instead of

... Password = "pword"

0 comments

Answer accepted by question author

Anonymous

Does this make a difference?

Private Sub CommandButton1_Click()

Sheet1.Protect Password:="pword"

Sheet2.Protect Password:="pword"

Sheet3.Protect Password:="pword"

End Sub

Private Sub CommandButton2_Click()

Sheet1.Unprotect Password:="pword"

Sheet2.Unprotect Password:="pword"

Sheet3.Unprotect Password:="pword"

End Sub

Gord

0 comments

4 additional answers

Answer 1

Password = "pword" can be used in two ways:

When used by itself, it assigns the value "pword" to a variable named Password.
When used in an If ... Then statement, it compares the current value of the variable Password to "pword", for example

If Password = "pword" Then MsgBox "Correct" Else MsgBox "Incorrect"

But in your code, Password is an argument of the Protect and Unprotect methods of the Worksheet object. To assign a value to an argument (as opposed to assigning a value to a variable), one must use := instead of =.

Answer 2

I absolutely made sure of the worksheet and the password. To make doubly sure, i used the same password to protect all of the worksheets. i also tried to cut and paste the text out of the VBA command and into the password box.

Neither worked.

As a test, I created a new spreadsheet with two buttons on sheet1 and these VBA Commands

Private Sub CommandButton1_Click()

Sheet1.Protect Password = "pword"

Sheet2.Protect Password = "pword"

Sheet3.Protect Password = "pword"

End Sub

and this :

Private Sub CommandButton2_Click()

Sheet1.Unprotect Password = "pword"

Sheet2.Unprotect Password = "pword"

Sheet3.Unprotect Password = "pword"

End Sub

I clicked each button and could verify that each sheet was protected or Un-Protected. However, if I tried to manually unprotect a sheet after running the first sub-routine it would give me an error. Conversely, if I manually protected a sheet, then ran the second sub-routine I would get an error.

Answer 3

HansV 462.7K MVP Volunteer Moderator

Protecting/unprotecting via code and interactively should be interchangeable. I've done this thousands of times.

Make sure that you're referring to the correct worksheet. Sheet1 in VBA is not necessarily the same as Worksheets("Sheet1")
Make sure that you're spelling the password the same way in VBA and interactively.

0 comments