Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    North Vancouver, Br. Columbia, Canada
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Status of Sheet Protection (Excel 2003)

    Is there an Excel function which will return the protection status of a sheet? The =Cell("protect",reference) function will return the locked status of the referenced cell, but I'm looking for the protected status for the sheet itself.

    Thanks,
    Archie

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Status of Sheet Protection (Excel 2003)

    You could create a custom function:
    <code>
    Public Function SheetProtection(Optional SheetName As String)
    If SheetName = "" Then
    SheetName = ActiveSheet.Name
    End If
    SheetProtection = Sheets(SheetName).ProtectContents
    End Function
    </code>
    Use in a formula as follows:
    <code>
    =SheetProtection()
    </code>
    This returns TRUE if the contents of the current sheet are protected, FALSE otherwise. Or use
    <code>
    =SheetProtection("Sheet3")
    </code>
    to see whether the contents of Sheet3 are protected.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Status of Sheet Protection (Excel 2003)

    You can create one in a module:

    <pre>Function SheetProtected(rng As Range)
    SheetProtected = rng.Parent.ProtectContents
    End Function</pre>


    Then put in the cell you want the result:
    <pre>=sheetprotected(Sheet1!A1)</pre>


    Steve

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Status of Sheet Protection (Excel 2003)

    I like the optional parameter aspect for the active sheet and I steal it here <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    I also think appl.volatile might be good since it won't be live when a sheet changes

    <pre>Function SheetProtected(Optional rng As Range)
    Application.Volatile
    If rng Is Nothing Then
    SheetProtected = ActiveSheet.ProtectContents
    Else
    SheetProtected = rng.Parent.ProtectContents
    End If
    End Function</pre>


    Yours has the advantage of working with chartsheets, but the disadvantage of having to type in the name of the sheet rather than just pointing to a cell. Mine will also still work if the sheet names are changed.

    Steve

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Status of Sheet Protection (Excel 2003)

    How about this? Probably overkill...
    <code>
    Public Function SheetProtected(Optional Ref As Variant) As Boolean
    Application.Volatile
    If IsMissing(Ref) Then
    Set Ref = ActiveCell
    End If
    Select Case TypeName(Ref)
    Case "Range"
    SheetProtected = Ref.Parent.ProtectContents
    Case "String"
    SheetProtected = Sheets(Ref).ProtectContents
    Case Else
    Err.Raise 1
    End Select
    End Function
    </code>
    You can use
    <code>
    =SheetProtected(Sheet3!A5)
    </code>
    or
    <code>
    =SheetProtected("Chart1")
    </code>
    or just
    <code>
    =SheetProtected()
    </code>
    This combines your and my approaches - you can use whatever is most convenient.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Status of Sheet Protection (Excel 2003)

    Or a defined name:

    IsProtected
    Referring to:
    =GET.DOCUMENT(6)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Status of Sheet Protection (Excel 2003)

    That doesn't seem to work for me - it always returns FALSE, whether the worksheet is protected or not.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Status of Sheet Protection (Excel 2003)

    Which proves I did not test this. Sorry for the mistake..
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Status of Sheet Protection (Excel 2003)

    I was thinking about this type of approach (combining the string/range) also. The only problem I saw was that you either had to select a range or give a literal string. I was "mulling over" the approach if you wanted to have the name of the sheet (as a string) in a cell and reference that and couldn't think of a way without using another parameter.

    But I guess we will have just to wait for Archie to decide if we gave him too much to think about or he wants an even more complicated function <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Steve

  10. #10
    New Lounger
    Join Date
    Jan 2001
    Location
    North Vancouver, Br. Columbia, Canada
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Status of Sheet Protection (Excel 2003)

    I have been distracted (momentarily), but have come back to the task requiring this function. I have used HansV's 13-Jan-05 16:03 SheetProtected function, and it works wonderfully for my purposes! I do appreciate all your suggestions!

    Thanks,
    Archie

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •