• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

Get Excel Sheet Protect/Unprotect property

How do I check with VBA whether a worksheet is Protected or not?

Thanks.
0
hindersaliva
Asked:
hindersaliva
  • 3
  • 2
1 Solution
 
dlmilleCommented:
This reference should help you:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=239

Dave
0
 
Brian GeeCommented:
This VBA code runs a test to see if columns are able to be inserted (it does not insert columns though). If so, then you will get a response that the sheet is unprotected otherwise a protected message.

Sub CheckProtected()
    ActiveSheet.Protect

    If ActiveSheet.Protection.AllowInsertingColumns = True Then
        MsgBox "The worksheet is unprotected."
    Else
        MsgBox "The worksheet is protected."
    End If
End Sub

Open in new window

0
 
dlmilleCommented:
Using my post, above, you should be able to check whether any sheet is protected.

I added the code - checkForProtectedSheets() to flag any sheet that IS protected in the active workbook:

 
Sub checkForProtectedSheets()
Dim mySheet As Worksheet

    For Each mySheet In ActiveWorkbook.Worksheets
        If SheetProtected(mySheet) Then
            MsgBox "Your Sheet: " & mySheet.Name & " is protected", vbOKOnly
        End If
    Next mySheet
End Sub

Open in new window


See attached,

Enjoy!

Dave
checkProtected-r1.xls
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
dlmilleCommented:
And - to make this a more simple answer -  all that code is really looking at this property ->  TargetSheet.ProtectContents, where TargetSheet is of type Worksheet.

if the ActiveSheet.ProtectContents = True, then the ActiveSheet is protected.

if Sheets("Whatever").ProtectContents = True, then THAT sheet is protected, etc.

Dave
0
 
Brian GeeCommented:
Sorry that I whipped up the VBA without sufficiently testing it.

Here is tested code that works:
Sub CheckProtected()

    If ActiveSheet.ProtectContents = True Then
        MsgBox "Protected"
    Else
        MsgBox "Not Protected"
    End If
    
End Sub

Open in new window

0
 
hindersalivaAuthor Commented:
Perfect thanks. (this is exactly what I did but it didn't work so hence the post in EE. Makes me wonder if there are scenarios in which the ProtectContents property returns the wrong result. Probably my error though!)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now