Home > Enterprise >  How to unprotect a worksheet if I made a syntax error while protecting it in the first place using E
How to unprotect a worksheet if I made a syntax error while protecting it in the first place using E

Time:03-30

A simple syntax error on my part. In a block of code, I had a line to protect a worksheet.

Sheets("xyz").Protect password = "abc", AllowFiltering:=True

Now I'm unable to unprotect the sheet using the password "abc". I realized I missed a : after password. Can anyone help figure out the password in this instance?

CodePudding user response:

So what you did is to write a boolean expression as password. VBA will evaluate the term password = "abc" to either True or False - likely to False except if you have somewhere a variable password declared that contains abc.

I couldn't figure out what exactly is written as password. Tried several variations and this is the outcome:

' Sheets("xyz").Unprotect "False"    ' Didn't work
' Sheets("xyz").Unprotect "0"        ' Didn't work
' Sheets("xyz").Unprotect 0          ' Didn't work
Sheets("xyz").Unprotect False        ' Works!

General advice: Define your password as (named) constant so you don't have to search the code for all protect/unprotect statements if one day you want to change it.

Const MySecret = "abc"
...
Sheets("xyz").Protect Password:=MySecret
...
Sheets("xyz").Unprotect Password:=MySecret

And I assume that you don't have a variable Password declared. In that case, using Option Explicit would have prevented that error because the compiler would have complained.

CodePudding user response:

If the following:

Sheets("xyz").Protect password = "abc"

locks the sheet, for the reasons FunThomas explains - then strictly speaking the following would unlock it:

Sheets("xyz").Unprotect password = "abc"
  • Related