Protecting sheet using cell value works, but not unprotecting? - confused

[karlv02]

Greetings Everyone!

I have a worksheet that i have been using a macro to protect/unprotect the sheets. Recently, i thought instead of having the password visible in my code, i thought i'd hide it in a veryHidden sheet. (i can't just hide the code, as i have granted this workbook access to VBA object).

Protecting seems fine, but unprotecting gives me an error telling me my password is incorrect, and i should check caps lock.

My protect code is:

Sub protect()Dim ws As Worksheet
Dim yomomma As String
yomomma = Sheet3.Range("B6").Text
For Each ws In Sheets
ws.protect password:=yomomma, UserInterfaceOnly:=True
Next ws
End Sub

and it works perfectly

My unprotect is essentially identical, and gave me the above error:

Sub unprotect()Dim ws As Worksheet
For Each ws In Worksheets
Dim yomomma As String
yomomma = Sheet3.Range("B6").Text
ws.unprotect password:=yomomma
Next ws
End Sub

I assume that this is a limitation of Excel VBA, but google isn't turning up anything relevant. (or i suck at google?).

Thanks in advance!

 

[StephenCrump]

It works fine for me ...?

The only way the next line should give you an error is if ws is protected with a different (case-sensitive) password.

ws.unprotect password:=yomomma

If ws is not protected, or if it's protected with no password, your password argument will be ignored.

So is there any possibility you have set the password differently for any sheet(s)?

Can you identify the sheet where unprotect() breaks down? Can you unprotect this sheet manually? Will your routines work on a brand new workbook?

Incidentally, I note that protect() loops through Sheets, and unprotect() loops through Worksheets. These aren't necessarily the same. Presumably your workbook doesn't have any Chart Sheets, otherwise protect() would have given you a type mismatch, because ws is dimensioned as Worksheet.

 

[karlv02]

hmm... looks like excel has gone and made a liar of me. Although i'm using 2013 at home and 2010 at work. I tested my code here, and "Sheets" or "worksheets" seemed to make no difference. I'll have to re-test tomorrow at work.

As far as when it was happening, it would give me the error the moment it tired to run the "ws.unprotect password:=yomomma" line of code, wouldn't even make it through the first sheet's unprotection.

i'll report back my findings.


Thanks Stephen.