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.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
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....
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.