Excel VBA: Check Whether a Sheet Is Protected

VBA & macros · Updated November 19, 2026

In Excel VBA, check Worksheet.ProtectContents to see whether a worksheet’s cells are protected. For a broader check that also detects protection of drawing objects or scenarios, test ProtectContents, ProtectDrawingObjects, and ProtectScenarios together.

Use VBA to check a worksheet

This macro checks a worksheet named Sheet1 in the workbook containing the code, then reports whether any of the three worksheet protection flags is on. Change the sheet name to match your workbook. Save a copy before running a macro; macros cannot be undone with Ctrl+Z, even though this example only reads protection settings.

Sub ReportSheetProtection()
    Dim ws As Worksheet
    Dim isProtected As Boolean

    ' Change Sheet1 to the worksheet you want to inspect.
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    ' Check cell contents, drawing objects, and scenarios.
    isProtected = ws.ProtectContents Or _
                  ws.ProtectDrawingObjects Or _
                  ws.ProtectScenarios

    If isProtected Then
        MsgBox ws.Name & " has worksheet protection enabled."
    Else
        MsgBox ws.Name & " has none of the three protection flags enabled."
    End If
End Sub

In the Visual Basic Editor, insert the code into a standard module, edit the worksheet name, and run the procedure. In desktop Excel for Windows, Alt+F11 opens the editor; on Mac, Option+F11 is the usual equivalent, though some keyboards require Fn as well. Excel for the web cannot run VBA macros.

What the protection properties mean

Excel exposes separate Boolean properties for different aspects of worksheet protection. Each property returns True or False, so VBA can test the state without trying to remove protection or entering a password.

  • ProtectContents indicates whether worksheet contents are protected. This is the property most people need when asking whether cells on a sheet are protected.
  • ProtectDrawingObjects indicates whether drawing objects, such as shapes, are protected.
  • ProtectScenarios indicates whether scenarios on the worksheet are protected.

There is no general worksheet property named Protected that reports every protection detail in one value. The combined test in the macro is therefore useful when “protected” means that any of those three categories is protected. If your task specifically concerns editing cells, use ProtectContents on its own; object or scenario protection does not necessarily mean cell contents are protected.

Choose the check that matches your task

The right condition depends on what the next step in your code needs to do. A broad “any protection” test is convenient for a status report, but it can be too broad for a procedure that edits cell values. Conversely, checking only cell contents can miss protection applied to objects or scenarios.

  • Before editing cells: check ws.ProtectContents. If it is True, the worksheet’s contents are protected.
  • To report any worksheet protection: combine all three flags with Or, as in the example.
  • To diagnose a specific restriction: inspect each flag separately and report which category is enabled.

For example, if a sheet protects shapes but not contents, the combined test returns True, while ws.ProtectContents returns False. That is not a contradiction: the checks answer different questions. Also, VBA’s Or operator evaluates its operands rather than stopping as soon as one is true, which is harmless for these property checks.

Check a named sheet safely

A worksheet name must match the tab name exactly, including spaces. If the sheet does not exist in the workbook referenced by ThisWorkbook, the assignment to ws raises an error. ThisWorkbook means the workbook where the macro code is stored; ActiveWorkbook means the workbook currently active in Excel. They may be different, so use the reference that matches your intention.

For code that may receive a name that is not present, look up the worksheet first and handle the missing-sheet case rather than allowing a run-time error to interrupt the procedure. The following pattern uses error handling only around the lookup, then checks whether the object was found.

Sub CheckNamedSheet()
    Dim ws As Worksheet
    Dim sheetName As String

    ' Replace this value with the requested tab name.
    sheetName = "Sheet1"

    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(sheetName)
    On Error GoTo 0

    If ws Is Nothing Then
        MsgBox "The worksheet was not found: " & sheetName
        Exit Sub
    End If

    If ws.ProtectContents Then
        MsgBox ws.Name & " protects worksheet contents."
    Else
        MsgBox ws.Name & " does not protect worksheet contents."
    End If
End Sub

Keep On Error Resume Next limited to the lookup. Leaving it active for the rest of a macro can hide unrelated problems, such as a misspelled property or an unexpected failure later in the procedure.

Worksheet protection is not workbook protection

Protecting a worksheet and protecting a workbook’s structure are different settings. Worksheet protection limits actions on a particular sheet according to the protection options selected. Workbook structure protection affects operations such as adding, deleting, moving, hiding, or renaming sheets. A workbook can have one type enabled without the other.

To check workbook structure protection, inspect the workbook’s ProtectStructure property, for example ThisWorkbook.ProtectStructure. Do not use that property as a substitute for checking a worksheet’s ProtectContents flag: it answers a different question. A workbook might also have window protection, represented by ProtectWindows, which is likewise separate.

Common interpretation mistakes

A True protection property does not tell you which cells are locked, which editing actions are allowed, or what password was used. It reports the state of that protection category. Worksheet protection works with cell settings such as the Locked property: cells are typically marked locked by default, but that setting only restricts editing when worksheet contents are protected. A protected sheet can still allow selected actions depending on the options chosen when protection was applied.

Protection is also not the same as encryption. It is intended to restrict editing within Excel, not to make workbook contents confidential against determined access. Do not treat a protected sheet as a secure way to store sensitive information.

Another frequent mistake is assuming that a protected worksheet must have a password. Excel can protect a sheet without a password, and the protection properties do not reveal whether one was supplied. The properties report whether the relevant protection is active, not its password or the complete set of permissions.

Using the check in a larger macro

A protection check is often a branch in a larger procedure. For instance, a macro that updates a report can test ProtectContents before attempting to write values, then stop with a clear message if the sheet is protected. Checking first makes the procedure’s behavior easier to understand, but it does not itself unlock the sheet or grant permission to edit it.

If your automation is designed to work with protected sheets, plan that behavior explicitly. A macro may be able to use the worksheet’s Unprotect method if it has the required password, perform its updates, and then reapply protection with the intended options. That changes the workbook, so it should be designed and reviewed carefully; do not add password-handling or protection changes merely to make a status check pass. Keep credentials out of code that will be shared unless you have assessed the security implications.

For a read-only audit, the three Boolean properties are enough to record the categories of protection currently reported by Excel. If a workbook contains many sheets, loop through its Worksheets collection and check each worksheet. Remember that a chart sheet is not a worksheet, which is one reason a variable declared as Worksheet is appropriate when inspecting worksheet protection.

Where the VBA check works

These worksheet properties are part of Excel’s VBA object model and are available in desktop Excel for Microsoft 365 on Windows and Mac. The VBA editor and macro permissions differ somewhat by platform, but the protection-property checks themselves use the same object-model names. Excel for the web can display a workbook, but it does not execute VBA; use desktop Excel to run the macro. Save a macro-containing workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain its VBA code.

Excel may block macros depending on the file’s origin and the organization’s security settings. Enable macros only for files you trust and in line with your organization’s policy. A protection check does not bypass macro security, and simply opening a workbook does not mean its VBA has run.

Frequently Asked Questions

What is the simplest VBA check for a protected sheet?

Use If Worksheets("Sheet1").ProtectContents Then when you mean protection of worksheet contents. It does not check drawing objects or scenarios, so include those properties if your definition of protection includes them.

Is there a Worksheet.Protected property?

No single Worksheet.Protected property reports all worksheet protection. Check ProtectContents, ProtectDrawingObjects, and ProtectScenarios for the categories relevant to your task.

Does ProtectContents tell me whether a password is set?

No. It indicates whether worksheet contents are protected, not whether a password was used. Excel’s protection properties do not provide the password.

Can VBA check protection without unprotecting the sheet?

Yes. Reading the protection properties does not require calling Unprotect and does not change the sheet’s protection state.

Does worksheet protection mean the workbook structure is protected?

No. Check a workbook’s ProtectStructure property for structure protection. Worksheet protection flags apply to a particular worksheet and describe different restrictions.

Related

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.