Cannot unlock worksheet after sheet is locked by VBA

wpryan

Well-known Member
Joined
May 26, 2009
Messages
534
Office Version
  1. 365
Platform
  1. Windows
Hi All, I have a worksheet that is unlocked by VBA to run some code, and then locked again. The password is based on a value in a cell on another worksheet. When I want to unlock manually, I get an error message that the supplied password is incorrect. If I run the code again, it works correctly. If I remark out the second "protect" line of code, the sheet remains unlocked. If I set the password manually and then change the password in the code to the same one, it runs correctly but if I try and unlock it manually, it doesn't work again. I can't figure out what's going on. The code is:

VBA Code:
Dim pw As String
pw = Sheets("Release").Range("A20").Value
-code here-
Sheets("Data").Unprotect Password = pw
-more code here-
Sheets("Data").Protect Password = pw
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
My guess is that the password string held in Sheets("Release").Range("A20") is not exactly the same as the password you enter manually. Maybe there is a non printable character or an empty space in the cell.

Run the following in the immediate window to see how many characters there are in cell A20. Does it return the same number of characters that you enter manually ?
?Len(Sheets("Release").Range("A20").Value)
 
Upvote 0
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

It may not be related to your problem, but the syntax in that code snippet is incorrect. It should be

Rich (BB code):
Dim pw As String
pw = Sheets("Release").Range("A20").Value
'-code here-
Sheets("Data").Unprotect Password:=pw
'-more code here-
Sheets("Data").Protect Password:=pw

With that syntax and a valid password in Release A20 the code works for me and I can manually unprotect the Data sheet.
 
Upvote 0
Solution
Was the password initially set by you or by someone else?

In addition to what suggested by JT, above, look at the attached minisheet:
Not only a non displayed character is added at the end of the string, but the "Space" is not an ascii space (see code in C9)
Cell Formulas
RangeFormula
A2A2="Strange"&CHAR(160)&"Password"&CHAR(10)
C2:C18C2=CODE(B2)
B2:B19B2=MID($A$2,ROW(A1),1)
 
Last edited:
Upvote 0
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

It may not be related to your problem, but the syntax in that code snippet is incorrect. It should be

Rich (BB code):
Dim pw As String
pw = Sheets("Release").Range("A20").Value
'-code here-
Sheets("Data").Unprotect Password:=pw
'-more code here-
Sheets("Data").Protect Password:=pw

With that syntax and a valid password in Release A20 the code works for me and I can manually unprotect the Data sheet.
Thanks for your reply. I am running Office 365.
Such a silly thing, forgetting the ":"
Thanks for your help!
 
Upvote 0
You're welcome.

If your code ran without the ":" then I think that you must not be using "Option Explicit" in your coding. I would recommend always using that as it will pick up many instances of incorrect variable names etc. You can force Option Explicit to always be in play with this setting in the vba window. You only have to apply the setting once.

1667782174344.png
 
Upvote 0
You're welcome.

If your code ran without the ":" then I think that you must not be using "Option Explicit" in your coding. I would recommend always using that as it will pick up many instances of incorrect variable names etc. You can force Option Explicit to always be in play with this setting in the vba window. You only have to apply the setting once.

View attachment 77987
Thanks for the tip. I was curious why the code worked.
 
Upvote 0
You're welcome. Thanks for the follow-up. :)
 
Upvote 0
Maybe the OP was asking why his "wrong" code worked...

It worked but was protecting the sheet with the wrong password, ie with the result of the operation Password = pw
And since "Password" was empty and "pw" had a value then the password set was "False"
You may test it with this code:
Code:
Sub Testtt
Dim pw As String
pw = "Anything"
'
Sheets("Data").Protect Password = pw        '1
Stop
Sheets("Data").Unprotect False              '2
End Sub
Run the macro; on the Stop check that the worksheet is protected
Resume the macro (either pressing F8 twice or pressing F5) and line "2" will uprotect it
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top