Hi,

does anyone know how to detect whether a sheet is Password
Protected as opposed to just Protected.

I do this by writing a function and returning true when I
attempt to unprotect it using an "on error". There must be
a better way...

Chris

This code here does what I do believe you are looking for it to do. It
is a little ugly using errors to test for the password but it works.

Sub CheckProtection()

If Sheet1.ProtectContents = True Then
MsgBox "I am protected!"
On Error GoTo MustBePassword
Sheet1.Unprotect "ZFNUefnisudr7tqw6etr90er" ''Password that
no one would ever use.
Sheet1.Protect ''Used to protect sheet back up if no
password protecting it.
On Error GoTo 0
Else
MsgBox "I am not protected!"
End If

AllDone:
Exit Sub

MustBePassword:
MsgBox "I am also Password Protected!!"
GoTo AllDone

End Sub


Hope that helps,
Keith
www.kjtfs.com


---
Message posted from http://www.ExcelForum.com/

Still relying on errors handling, but perhaps a little cleaner:

Public Function CheckProtection(ByRef wkSht As Worksheet) As Long
CheckProtection = wkSht.ProtectContents
If CheckProtection Then
On Error Resume Next
wkSht.Unprotect ""
On Error GoTo 0
If wkSht.ProtectContents Then
CheckProtection = -CheckProtection
Else
wkSht.Protect
End If
End If
End Function


This will return the following:

0/False if not protected
-1/True if protected without a password
1 if protected with a password.




If ActiveSheet.ProtectCIn article ,
KJTFS wrote:

This code here does what I do believe you are looking for it to do. It
is a little ugly using errors to test for the password but it works.

Sub CheckProtection()

If Sheet1.ProtectContents = True Then
MsgBox "I am protected!"
On Error GoTo MustBePassword
Sheet1.Unprotect "ZFNUefnisudr7tqw6etr90er" ''Password that
no one would ever use.
Sheet1.Protect ''Used to protect sheet back up if no
password protecting it.
On Error GoTo 0
Else
MsgBox "I am not protected!"
End If

AllDone:
Exit Sub

MustBePassword:
MsgBox "I am also Password Protected!!"
GoTo AllDone

End Sub


Hope that helps,
Keith
www.kjtfs.com


---
Message posted from http://www.ExcelForum.com/