Determining When An Excel Spreadsheet Is Protected

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.

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).

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.

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 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.

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.

More Articles On This Topic

• VBA Function To Determine If Any Worksheet Tabs Are Protected
• How To Determine If A Workbook or Worksheet Is Password Protected With Excel VBA
• Insert & Delete Table Rows With Worksheet Protection
• The Various Ways To Password Protect Excel Worksheets
• Understanding Excel’s Password Security Methodology

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.

Format Excel Charts Like a Pro!!!

Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!

Get These Hacks Now For Free!

Keep Learning

Explore more →

Formulas To Calculate Longest & Current Win Streaks

All About The Wins As we start to head into the summer months here in the United States, I thought...

How To Build Waterfall (Bridge) Charts In Excel

Waterfall/Bridge charts are commonly used in the finance world to show up and down variances between two periods of time....

How To Vertically Center A Userform Textbox

There’s No Middle Alignment Option… Let’s just get straight to the point….unfortunately for some (lame) reason, Microsoft did not give...

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.