VBA Check If a Worksheet Is Protected

In VBA, check a worksheet’s ProtectContents property: Worksheets("Data").ProtectContents returns True when that worksheet’s cell contents are protected and False otherwise. For a broader check that also includes drawing objects and scenarios, test all three protection properties.

The quickest VBA check

Use this procedure in a standard VBA module to report whether the active sheet’s contents are protected. It first confirms that the active tab is a worksheet, since a workbook can also have a chart sheet active.

Save a copy of the workbook before running a macro. VBA actions generally cannot be reversed with Ctrl+Z.

Sub CheckActiveWorksheetProtection()
    Dim ws As Worksheet
    Dim message As String

    ' Make sure the selected tab is a worksheet, not a chart sheet.
    If Not TypeOf ActiveSheet Is Worksheet Then
        MsgBox "The active tab is not a worksheet.", vbExclamation
        Exit Sub
    End If

    Set ws = ActiveSheet

    If ws.ProtectContents Then
        message = ws.Name & " has protected contents."
    Else
        message = ws.Name & " does not have protected contents."
    End If

    MsgBox message, vbInformation, "Protection status"
End Sub

On Windows, open the Visual Basic Editor from Developer > Visual Basic, then choose Insert > Module and paste the code into the module. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon. Excel for the web does not run VBA macros; use desktop Excel. VBA is also available in Excel for Mac, though ribbon and editor access can vary with the version and keyboard.

What ProtectContents means

ProtectContents is a Boolean property of an Excel Worksheet object. It answers a focused question: are the worksheet’s contents protected? It does not return a password, list which cells are locked, or describe every permission granted to users. The property is read-only, so use it to inspect status rather than to turn protection on or off.

In the Excel interface, the command is usually called Protect Sheet. Sheet protection is most useful for preventing accidental edits to locked cells while still allowing selected actions. A sheet can be protected even when certain operations, such as selecting unlocked cells or using an allowed filter, remain possible. So ProtectContents = True does not mean that every action on the sheet is blocked.

Cell formatting also matters: cells are marked locked or unlocked through their Format Cells settings, but that setting only restricts editing when sheet protection is active. If the sheet is unprotected, the Locked setting alone does not stop a user from changing a cell.

Check contents, objects, and scenarios

Excel exposes separate properties for the main kinds of worksheet protection. A worksheet may have protection applied to its contents, drawing objects, or scenarios. If your question is simply whether cell contents are protected, use ProtectContents. If you mean whether any of those protection categories is active, combine the properties:

Function IsAnyWorksheetProtectionEnabled(ByVal ws As Worksheet) As Boolean
    ' True if any of the three worksheet protection categories is active.
    IsAnyWorksheetProtectionEnabled = _
        ws.ProtectContents Or _
        ws.ProtectDrawingObjects Or _
        ws.ProtectScenarios
End Function

Call the function with a worksheet reference, for example IsAnyWorksheetProtectionEnabled(ThisWorkbook.Worksheets("Data")). The sheet named Data must exist in the workbook referenced by ThisWorkbook, or VBA will raise an error when it tries to find it. In a regular macro, you can assign the sheet to a variable first and pass that variable to the function.

This combined test is useful when auditing how a sheet is configured, but it is not a substitute for checking the specific protection category that matters to your task. For example, a result of True could be caused by drawing-object protection while ProtectContents is False.

Check a named worksheet rather than the active tab

Relying on ActiveSheet means the result depends on which tab is selected when the macro runs. That can be inconvenient in a workbook with many sheets or when a macro is triggered from a button. Refer to the intended worksheet directly to avoid checking the wrong tab:

Sub CheckDataSheetProtection()
    Dim ws As Worksheet

    ' ThisWorkbook means the workbook containing this VBA code.
    Set ws = ThisWorkbook.Worksheets("Data")

    If ws.ProtectContents Then
        MsgBox ws.Name & " is protecting its contents."
    Else
        MsgBox ws.Name & " is not protecting its contents."
    End If
End Sub

Use ThisWorkbook when the macro should check the workbook that contains the code. ActiveWorkbook instead refers to the workbook currently active in Excel, which might be a different file. Choosing the right reference prevents a common mistake: a macro stored in a personal macro workbook or add-in can otherwise inspect a workbook other than the one that holds the target sheet.

Worksheet names must match the tab name exactly, including spaces and punctuation. If the target sheet may be renamed or may not exist, add error handling or find the sheet by another stable identifier before checking its property.

Interpreting the result correctly

A protection check reports the current state; it does not explain how that state was reached. True does not reveal whether protection was set by a user, a macro, or another process. Nor does it show the password used to protect the sheet. Excel does not provide a supported VBA property that returns a worksheet’s protection password.

True: the property being checked is active for that worksheet.
False: that particular protection category is not active. Other categories may still be protected.
Allowed actions: protection can be configured to permit some actions, so a protected sheet may still allow filtering, sorting, or editing unlocked cells.
Cell lock state: a cell’s Locked setting is separate from the sheet’s current protection status.

For example, if ProtectContents is True, it does not follow that every cell is locked. Some cells may have been set to unlocked before protection was applied. Conversely, cells can be marked locked while ProtectContents is False; in that state, the lock setting is not enforcing an editing restriction.

Worksheet protection is not workbook protection

Excel has distinct protection features that are easy to confuse. Worksheet protection applies to a particular sheet. Workbook-structure protection restricts changes such as adding, deleting, moving, hiding, or renaming sheets. Protecting the workbook file with encryption is different again: it concerns opening the file, not just restricting edits within a sheet.

To inspect workbook structure protection, check the workbook’s ProtectStructure property, rather than a worksheet property. For example, ThisWorkbook.ProtectStructure returns a Boolean indicating whether the workbook structure is protected. A workbook can have protected structure while an individual sheet’s contents are unprotected, or the reverse. Check the property that matches the question you need to answer.

None of these status checks proves that a workbook is secure against someone determined to alter it. Excel sheet protection is primarily an editing-control feature, not a replacement for file encryption, access controls, or secure storage. Avoid treating a True result as evidence that sensitive information cannot be accessed.

Common problems and troubleshooting

Object required or type mismatch: the active tab may be a chart sheet, or the variable may not have been set to a worksheet. Use a declared Worksheet variable and confirm the object reference.
Subscript out of range: a sheet name in Worksheets("Data") does not match a sheet in the workbook being referenced. Check the tab name and whether you meant ThisWorkbook or ActiveWorkbook.
The macro says False, but cells look locked: a cell’s Locked setting does not enforce protection while the sheet itself is unprotected. Check the sheet status separately.
The macro says True, but users can still do something: that may be allowed by the sheet’s protection options, or the cells involved may be unlocked. The Boolean property does not enumerate permitted actions.
The macro will not run: use desktop Excel and confirm that macros are permitted by your organization’s security settings. Do not enable macros in an untrusted workbook just to inspect its protection.

When debugging, set a breakpoint or use the VBA editor’s Immediate window to inspect a property while the workbook is open. For example, after selecting a worksheet variable named ws, evaluating ? ws.ProtectContents prints True or False. The Immediate window is a diagnostic tool; the question mark is not part of a VBA statement to paste into a normal procedure.

When to use VBA, the interface, or another check

For one-time inspection, Excel’s Review tab provides the visual route: the sheet-protection command changes between Protect Sheet and Unprotect Sheet depending on the current state. The ribbon is convenient when you only need to look at one sheet, while VBA is helpful for repeated checks, reports, or branching logic in a macro. Exact button labels can vary slightly by Excel build and ribbon layout.

For a macro that should stop before changing a protected sheet, test the property before making edits. That is safer than attempting an operation and assuming every error means the sheet is protected: errors can also come from invalid ranges, missing sheets, or other causes. If the macro’s purpose is to unprotect and then re-protect a worksheet, design that workflow deliberately and account for passwords and allowed actions; do not assume the status property can recover a password.

If a workbook contains many sheets, a loop can check each worksheet and write results to a report, but the same distinction still applies: decide whether the report should track contents only or all three worksheet protection categories. Checking every sheet’s ProtectContents value alone does not report object- or scenario-only protection.

Frequently Asked Questions

What is the VBA property to check whether a sheet is protected?
Use Worksheet.ProtectContents to check whether the worksheet’s contents are protected. It returns True or False.

How do I check if any part of a worksheet is protected?
Combine ProtectContents, ProtectDrawingObjects, and ProtectScenarios with the VBA Or operator. A combined True result means at least one of those categories is protected.

Can VBA tell me the sheet-protection password?
No. The protection-status properties do not reveal a password. Do not rely on a status check to retrieve credentials or bypass protection.

Does ProtectContents mean every cell is locked?
No. It reports whether contents protection is active for the sheet. Individual cells can have different Locked settings, and protection options can allow some actions.

Why is ProtectContents False when the cells are set to Locked?
The Locked cell setting only restricts editing when worksheet contents protection is turned on. If the sheet is not protected, locked cells remain editable.

Can I run this VBA check in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to use the VBA code.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.