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