Excel Sheet Protection Check

Detect Sheet Protection

Whenever working with a worksheet ever wondered why some menu buttons are not working or ribbon icons are greyed out and no functionality doesn’t work, this happened to me and because of a silly mistake.

I can’t say how many times this has happened to me while working on a spreadsheet and no realizing that it was being protected. I was working on a spreadsheet and realized I cannot copy data from the sheet, and why so? Because my spreadsheet was being protected.

Let’s see how we can figure out some of the ways we can check if the sheets are protected in the workbook and to get a list of all the sheets being protected.

Check the Ribbon

1. If something doesn’t feel right with the sheet and you are not able to make any edits in the sheet check if the sheet is being protected or not if the sheet is being protected, you can move to the REVIEW TAB on the excel ribbon and check out the PROTECT BUTTON, If it says UNPROTECT SHEET, well you guessed it right, your sheet is being protected.
2. Another way is to check if you have access to the ribbon buttons and all the buttons are not greyed out if all of the buttons are greyed out/ disabled meaning the sheet you are working on is being protected. The Home tab buttons are disabled.
3. Another way is when you right-click on the sheet the menu that pops-up has disabled buttons, this means that sheet protection is being used on this sheet and you need to turn it off in order to work with this sheet.

VBA Function to check if the sheet is protected

This VBA code below checks if the Active Sheet is protected or not and returns are True value if protected and False if not protected, a very handy way and easy to check way, to check if the sheet is protected.

'check if the sheet has protection on
Function protected(sheet As Worksheet) As Boolean
protected = sheet.ProtectContents
End Function

Or we can write some VBA macro which can even check and return the names of the sheets that are being protected in this Active workbook

'give a list of all protected sheets
Sub protectedSheets()
Dim sheet As Worksheet
Dim visible As String
Dim hidden As String
For Each sheet In ActiveWorkbook.Worksheets
If sheet.ProtectContents = True Then
If sheet.Visible = xlSheetVisible Then
visible = visible & vbNewLine & " - " & sheet.Name
Else
hidden = hidden & vbNewLine & " - " & sheet.Name
End If
End If
Next sheet
'results
If hidden = "" And visible = "" Then
MsgBox "No worksheets protected"
Else
MsgBox "Protected Worksheets: " & _
vbNewLine & vbNewLine & "Visble :" & visible & _
vbNewLine & vbNewLine & "Hidden :" & hidden, , "Summary"
End If
End Sub

After this macro is used on a workbook it will list all of the sheets that are protected and checks all the sheets either hidden or visible.

Hopes this helps you in not making silly mistakes and not waste your time, if you have questions or suggestions, please let us know, Happy to help. ThankYou