Sheet Protection?

[Don Wells]

I am running 2003 and am attempting to use the following code to determine whether the active sheet is an unprotected worksheet or not. The active sheet is a protected worksheet (as shown by the attached figure), yet the following function returns False. Any ideas?

Code: Select all

Public Function Cht_Or_Protected() As Boolean
Dim ws As Worksheet
  
  On Error Resume Next
  Set ws = ActiveSheet
  If Err Then
    On Error GoTo 0
    Cht_Or_Protected = True
    GoTo ExitHandler
  End If
  Cht_Or_Protected = ws.ProtectionMode
  
ExitHandler:
  Set ws = Nothing
End Function

[Goshute]

Don, without testing, I think you can Set a sheet as an Object in VBA, whether or not it's protected. How come you aren't using:

Worksheets(ActiveSheet.Name).ProtectContents

[HansV]

According to the Excel VBA help, ProtectionMode returns something different from what you want:

True if user-interface-only protection is turned on. To turn on user interface protection, use the Protect method with the UserInterfaceOnly argument set to True. Read-only Boolean.

To test whether a sheet has been protected, use the ProtectContents property.

True if the contents of the sheet are protected. This protects the individual cells. To turn on content protection, use the Protect method with the Contents argument set to True. Read-only Boolean.

[HansV]

Worksheets(ActiveSheet.Name).ProtectContents

Or simply ActiveSheet.ProtectContents:

Code: Select all

Public Function Cht_Or_Protected() As Boolean
  Cht_Or_Protected = ActiveSheet.ProtectContents
End Function

[Goshute]

Worksheets(ActiveSheet.Name).ProtectContents

Or simply ActiveSheet.ProtectContents

True. For that matter I don't see a need for a function; it's a one-line test, and I rarely need to use it more than once in a sub.

EDIT - figured out why I used the longwinded Worksheets(ActiveSheet.Name).ProtectContents syntax - it was inside a loop of
ActiveWindow.SelectedSheets(intCounter), and within ActiveWindow you have to activate each sheet ... I think...

[Don Wells]

Hans and Goshute
    Thank you both
    It speaks well for your patience that you didn't reply with "RTFM".