×

Determining When An Excel Spreadsheet Is Protected

By Chris Newman •  Updated: 05/25/20 •  5 min read
Excel Spreadsheet Protection Guide

Detecting Sheet Protection

I can’t count how many times I have been completely confused while working in somebody else’s spreadsheet only to discover my issue was being caused due to a spreadsheet being protected (it’s even worse with the spreadsheet is hidden). Let’s review some ways you can quickly figure out if you have sheets protected in your workbook.


Sheet Icon (New for 2020!)

This is an exciting little feature that got released in May 2020 via the Insider version of Excel (aka public beta version). Any time a worksheet has protection enabled, a padlock icon shows in front of the tab name. This is a great little indicator to easily understand if a worksheet is protected or not. I suspect this handy little feature will hit the monthly channel in a couple of months as long as they do not find any issues with it.

Excel Password Icon Tab Name

My Wish List: Unfortunately, as of this writing, the padlock icon does not appear within the Unhide dialog, but I sent a little mockup to Microsoft and suggested it (shown below).

Unhide Dialog With Padlock Icon

Unprotect Button Appears

If you suspect the sheet you are viewing is protected, you can navigate to the Review tab on your Excel Ribbon and checkout the Protect buttons. If the first button’s name is Unprotect Sheet, you know your ActiveSheet is currently protected.

Unprotect vs Protect Button Excel Ribbon

Disabled Ribbon Buttons

Another way to infer there might be some sort of sheet protection enabled is if you notice many of your buttons on your Excel Ribbon are disabled. Notice in the example below how nearly any button that deals with formatting is grayed out on the Home tab.

Disabled Excel Ribbon Buttons

Disabled Menu Buttons

Similar to buttons in your Ribbon being disabled, sheet protection can also causing menu you buttons to become disabled when you right-click. If you notice a bunch of your buttons grayed out, chances are theirs some sort of sheet protection that has been activated.

Disabled Excel Menu Items

Use VBA To Display A List

You can also utilize VBA code to perform tests or even provide a summary list of any protected sheets within your workbook.

Below is a simple function called IsProtected that provides a True/False result for the worksheet object that is passed through it.

Function IsProtected(sht As Worksheet) As Boolean
'PURPOSE: Determine if a specific Sheet has protection enabled

'Test if Sheet is Protected (T/F)
  IsProtected = sht.ProtectContents

End Function

You could also write a more versatile VBA macro that searches through all the tabs in your ActiveWorkbook and summarizes any protection found.

Sub SheetProtectionSummary()
'PURPOSE: List out all sheets that have protection enabled
'SOURCE: www.thespreadsheetguru.com/the-code-vault

Dim sht As Worksheet
Dim VisibleSheetList As String
Dim HiddenSheetList As String

'Loop through each sheet and test for protection
  For Each sht In ActiveWorkbook.Worksheets
  
    If sht.ProtectContents = True Then
      If sht.Visible = xlSheetVisible Then
        VisibleSheetList = VisibleSheetList & vbNewLine & "  - " & sht.Name
      Else
        HiddenSheetList = HiddenSheetList & vbNewLine & "  - " & sht.Name
      End If
    End If
  
  Next sht

'Display Results
  If HiddenSheetList = "" And VisibleSheetList = "" Then
    MsgBox "No worksheets were found to currently be protected in this workbook"
  Else
    MsgBox "The following worksheets were found to have sheet protection enabled:" & _
      vbNewLine & vbNewLine & "Visble Worksheets:" & VisibleSheetList & _
      vbNewLine & vbNewLine & "Hidden Worksheets:" & HiddenSheetList, , "Protection Summary"
  End If

End Sub

After running this macro on a Workbook, you can see the results displayed in a message box (shown below). You’ll notice that in this particular VBA code, I’ve grouped the protected sheet name results depending on if they are hidden to the user or not.

Protection Summary VBA Message Box


I Hope This Helped!

Hopefully, I was able to explain how you can determine is a sheet in Excel is protected or unprotected. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.

Keep Learning

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.