How To Check If a Worksheet Or Workbook Is Protected In Excel?

When working with Microsoft Excel, you find that some tools and features aren't working as usual. This may happen because the worksheet or the workbook in Excel is protected. Protecting a worksheet is a common practice to keep information safe before sharing. It prevents other users from changing, modifying, or deleting data accidentally or on purpose.

This tutorial explains how to check if you have a protected worksheet or workbook in MS Excel in three different methods.

Method 1

Examining Visual Features To Determine If The Worksheet Is Protected

By examining a few indicators in the worksheet, you can tell whether the sheet is protected or not. In the example workbook, there are 3 worksheets. Follow the steps mentioned in this method, and you will know the protection status of each worksheet in just a few seconds.

Step 1 − Navigate to the sheet tab at the bottom of the Excel window, placed above the status bar.

Step 2 − In the sheet tab, look for the padlock icon. If protection is enabled, the padlock icon (🔒) will appear before the sheet name in the tab.

Following the above steps, we see sheets 1 and 3 are protected.

Method 2

Checking The Features In The Excel Worksheet Ribbon

A protected sheet cannot be deleted or modified. When a sheet is protected, Excel automatically disables formatting buttons, and the cells become read-only. We show this by contrasting the MS Excel Ribbon of an unprotected sheet labelled "X Series" with the Ribbon of a protected sheet labelled "S Series."

Step 1 − Checking the formatting buttons in the first spreadsheet.

Step 2 − To determine whether the second spreadsheet in the workbook is protected, check the formatting options in the Excel Ribbon.

Method 3

Using VBA Code to Check If a Worksheet Or Workbook Is Protected In Excel

VBA codes offer more specific results and allow you to determine whether worksheets are protected in the active workbook quickly. We will show you in the following steps how to run the MS Excel VBA code using the sample worksheet above.

Step 1 − Press the Alt + F11 keys to open the Microsoft Excel Visual Basic Applications (VBA) dialogue box.

Step 2 − Right-click on "Microsoft Excel Objects", and from the drop-down menu, select Insert → Module.

Step 3 − Type in or paste the VBA code to the Module Editor section, which is the white box on the top right side of the window.

Microsoft Excel VBA code to check if worksheets in a workbook is protected −

Sub SheetProtectionSummary()
'PURPOSE: List out all sheets that have protection enabled

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

Click the run program button to see the result of the VBA code. A dialog box will appear displaying the sheets protected in the workbook.

VBA codes provide a more versatile way of searching through multiple worksheets in a workbook and a summary of any protections found. Applying the above steps, we see the sheets protected in one go.

Conclusion

Microsoft Excel is a very efficient software for calculating, analysing, storing, and protecting data. In this tutorial, we learn 3 methods of checking if the worksheet or workbook is protected. For more such tutorials that make Excel easy for you, read more here.