I have a macro I want to send out to multiple locations, but there are two
possible passwords for the sheet I need to manipulate.
I would like to have the macro test the sheet to see if it is password
protected, if it is, try the first password, test to see if it was unlocked,
if so, write that password to the password string (to reprotect using the
same password). If the test shows the sheet is still locked, then try the 2nd
password (I'll include both passwords in the script), then test again, if
unlocked, then write the 2nd password to the string.

I've come up with the following code, but it's not doing anything. I think
the variable is wrong. Any ideas?

'Start Password Checker
With ActiveSheet
If ActiveSheet.ProtectionMode Then ' not working, it's always True
..Unprotect password:="pass1"
If ActiveSheet.ProtectionMode = False Then
password = "pass1"
Else
.Unprotect password:="pass2"
password = "pass2"
End If
End If
End With

ActiveSheet.Unprotect (password)

'End Password Checker

David,

You have to check what method of protection is applied to the
ActiveSheet and the available methods vary between the different
versions of Excel97, 2000, 2003.

For future reference if you want to change things using VBA code but
want the sheet protected to the user you can protect the sheet using
the UserInterfaceOnly method.

Assuming that the ActiveSheet has been 'generally' protected the
following code should work. The variable 'pwd' will contain the actual
password used to unprotect the sheet.

Sub Password_Checker()

Dim pwd As String

On Error Resume Next
With ActiveSheet
If ActiveSheet.ProtectContents = True Then
.Unprotect Password:="pass1"
If ActiveSheet.ProtectContents = False Then
pwd = "pass1"
Else
.Unprotect Password:="pass2"
pwd = "pass2"
End If
End If
End With

End Sub

Regards

Michael Beckinsale

Thank you very much....it works perfectly! I did not write the original code
or create the worksheets, but your suggestion is noted. Thanks again!

"michael.beckinsale" wrote:


David,

You have to check what method of protection is applied to the
ActiveSheet and the available methods vary between the different
versions of Excel97, 2000, 2003.

For future reference if you want to change things using VBA code but
want the sheet protected to the user you can protect the sheet using
the UserInterfaceOnly method.

Assuming that the ActiveSheet has been 'generally' protected the
following code should work. The variable 'pwd' will contain the actual
password used to unprotect the sheet.

Sub Password_Checker()

Dim pwd As String

On Error Resume Next
With ActiveSheet
If ActiveSheet.ProtectContents = True Then
.Unprotect Password:="pass1"
If ActiveSheet.ProtectContents = False Then
pwd = "pass1"
Else
.Unprotect Password:="pass2"
pwd = "pass2"
End If
End If
End With

End Sub

Regards

Michael Beckinsale