Check if Workbook or Worksheet is Protected
November 2, 2008 • JP • No Comments • Rate This Article
• Links to this article
I was looking for a function to check if a worksheet is protected. I googled the appropriate phrase ("check if worksheet is protected") and followed the first link: an Excel function posted at vbaexpress.com: VBA Express : Excel – Check if Worksheet is protected.
Here is a copy of the code:
'Function purpose: To evaluate if a worksheet is protected
If TargetSheet.ProtectContents = True Then
SheetProtected = True
Else
SheetProtected = False
End If
End Function
Works well, but what about when you want to check if a whole workbook is protected? You need a second function. And what if you want to check both at the same time? Or a Range? Looks like it's time to roll your own!
This function takes an Excel.Workbook, Excel.Worksheet, or Excel.Range Object as an argument, and returns TRUE if any one is protected. Let me explain.
If you pass a Worksheet Object, it will simply check if the worksheet is protected. If you pass a Workbook Object, it will check if the workbook is protected, but will also check each of the worksheets for protection.
If you pass a Range Object, it checks for two conditions:
- If the cell is locked and the worksheet is protected (both are required for a cell to be uneditable), then it's protected
- If the workbook is protected (by recursively calling itself to check if the Workbook object is protected)
Dim wksht As Excel.Worksheet
Dim cell As Excel.Range
Select Case TypeName(objXL)
Case "Worksheet"
If objXL.ProtectContents Then
' still doesn't mean you can't edit the worksheet!
IsProtected = True
Exit Function
End If
Case "Workbook"
If objXL.ProtectStructure Then
IsProtected = True
Exit Function
End If
For Each wksht In objXL.Worksheets
If wksht.ProtectContents Then
' still doesn't mean you can't edit the worksheet!
IsProtected = True
Exit Function
End If
Next wksht
Case "Range"
If objXL.Cells.Count = 1 Then
If (objXL.Locked And objXL.Parent.ProtectContents) Or (IsProtected(objXL.Parent.Parent)) Then
IsProtected = True
Exit Function
End If
Else
For Each cell In objXL
If (cell.Locked And cell.Parent.ProtectContents) Or (IsProtected(cell.Parent.Parent)) Then
IsProtected = True
Exit Function
End If
Next cell
End If
End Select
End Function
Usage:
Set wkb = ActiveWorkbook
If IsProtected(wkb) Then
Msgbox "It's protected!"
End If
' or
Dim wksht As Excel.Worksheet
Set wksht = ActiveSheet
If IsProtected(wksht) Then
Msgbox "It's protected!"
End If
' or
Dim rng as Excel.Range
Set rng = Range("A3")
If IsProtected(rng) Then
Msgbox "It's protected!"
End If
The function should return FALSE for any other type of Object you pass to it, so be sure to only pass Workbook or Worksheet or Range Objects to it.
I got a bit lost writing this function, because of the numerous conditions for worksheet protection, and just settled with what I wrote above.
For example, just because a Worksheet Object is protected, doesn't mean you can't edit cells on the worksheet; if the cells are unlocked, you can still update cells on a protected worksheet. One way to check for that is by checking the Locked property of the Cells Object: If Cells.Locked = True, then all the cells on the worksheet are locked (IsProtected = True), if Cells.Locked = False, none of them are (IsProtected = False ?), and if there is a mix of locked and unlocked cells, Cells.Locked = Null (which is not a boolean value!)
And if you pass a Range object, does it matter if the workbook structure is protected? It depends on your specific needs.
And should Cells.Locked = False return IsProtected = False? It depends on the purpose of the function. If you just want a basic check for protection, it may be enough just to check if the worksheet is protected. But if you have specific needs, you might want to know not only if the worksheet is protected, but also if the cells are locked. Here's an updated version of the above function that does that.
Dim wksht As Excel.Worksheet
Dim cell As Excel.Range
Select Case TypeName(objXL)
Case "Worksheet"
If objXL.ProtectContents Then
' still doesn't mean you can't edit the worksheet!
Select Case Cells.Locked
Case True ' all cells are locked AND worksheet is protected
IsProtected = True
Exit Function
End Select
End If
Case "Workbook"
If objXL.ProtectStructure Then
IsProtected = True
Exit Function
End If
For Each wksht In objXL.Worksheets
If wksht.ProtectContents Then
' still doesn't mean you can't edit the worksheet!
Select Case Cells.Locked
Case True ' all cells are locked AND worksheet is protected
IsProtected = True
Exit Function
End Select
End If
Next wksht
Case "Range"
If objXL.Cells.Count = 1 Then
If (objXL.Locked And objXL.Parent.ProtectContents) Or (IsProtected(objXL.Parent.Parent)) Then
IsProtected = True
Exit Function
End If
Else
For Each cell In objXL
If (cell.Locked And cell.Parent.ProtectContents) Or (IsProtected(cell.Parent.Parent)) Then
IsProtected = True
Exit Function
End If
Next cell
End If
End Select
End Function
Enjoy,
JP
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Check your generated emails for valid recipients
Next Post: Using Excel VBA to set up Task Reminders in Outlook
Speak Your Mind
Tell us what you're thinking...Certain comments (including first-time comments) are subject to moderation and will not appear immediately. Please view the Comment Policy for more information. To post VBA code in your comment, use tags like this: [cc lang='vb']Code goes here[/cc].