Protection Reporter: See Exactly Which Sheets Are Locked Before You Start Unlocking

TL;DR: You inherit a workbook where some sheets are locked, some aren’t, and you can’t tell which is which without right-clicking every tab. This macro scans the entire workbook — protection status, password detection, editable ranges — and compiles it all into a single “Protection-Report” sheet. Run it before you run Sheet Lock/Unlock so you know exactly what’s protected and where.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook with mixed protection states — some sheets locked, some unlocked, some with passwords, some with editable ranges
• Run this before Sheet Lock / Unlock to understand what you’re dealing with

What the macro does:

• Loops every sheet in ThisWorkbook and checks protection properties
• Reports: protection status (Yes/No), password presence (Yes/No), editable ranges count, and a cell-lock sample check
• Creates a new “Protection-Report” sheet at the front of the workbook
• Leaves every sheet exactly as it was — no protection changed, no cells modified

What the macro does NOT do:

• It does not unlock anything. This is a read-only report. Run Sheet Lock / Unlock for that
• It does not reveal actual passwords. Password detection works by trying an empty string unlock and checking if it succeeds — it tells you a password exists, not what it is
• It does not check ProtectDrawingObjects or ProtectScenarios — these are rarely relevant in tax workpapers. Add them to the output if your firm uses them

Limitations:

• Works on ThisWorkbook — the workbook containing the macro. Store in Personal Macro Workbook (PERSONAL.XLSB) to audit any open file
• Password detection uses On Error Resume Next — if Excel’s protection API is in a locked state, the test may falsely report a password. This is rare but possible
• Editable range count requires Excel’s Protection.AllowEditRanges object, available in Excel 2010+. In older versions the count will show as “N/A”

#The Macro

Option Explicit

Sub ProtectionReporter()
    ' ── Protection Reporter ────────────────────────────
    ' Scans every sheet for protection status,
    ' password presence, editable ranges, and cell-lock
    ' state. Creates a read-only "Protection-Report"
    ' sheet. Zero modification to original data.
    '
    ' Run before Sheet Lock/Unlock to understand what
    ' you're working with.
    ' ────────────────────────────────────────────────────

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet
    Dim rpt As Worksheet
    Dim row As Long
    Dim protCount As Long
    Dim hasPW As String
    Dim editRanges As String
    Dim cellLocked As String

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    ' ── Step 1: Create the report sheet ────────────────
    Set rpt = ThisWorkbook.Sheets.Add(Before:=ThisWorkbook.Sheets(1))
    rpt.Name = "Protection-Report"

    ' Header row
    rpt.Cells(1, 1) = "Sheet Name"
    rpt.Cells(1, 2) = "Protected"
    rpt.Cells(1, 3) = "Has Password"
    rpt.Cells(1, 4) = "Editable Ranges"
    rpt.Cells(1, 5) = "Cells Locked"

    ' Format header
    With rpt.Rows(1)
        .Font.Bold = True
        .Interior.Color = RGB(220, 230, 241)
    End With

    ' ── Step 2: Scan every sheet ────────────────────────
    protCount = 0
    row = 2

    For Each ws In ThisWorkbook.Worksheets
        ' Sheet name
        rpt.Cells(row, 1) = ws.Name

        ' Protection status
        If ws.ProtectContents Then
            rpt.Cells(row, 2) = "Yes"
            protCount = protCount + 1

            ' Password detection: try unlocking with empty string
            On Error Resume Next
            ws.Unprotect ""
            If Err.Number <> 0 Then
                hasPW = "Yes"
                Err.Clear
            Else
                hasPW = "No"
                ' Re-protect (no password)
                ws.Protect ""
            End If
            On Error GoTo CleanUp
            rpt.Cells(row, 3) = hasPW

            ' Editable ranges count
            On Error Resume Next
            editRanges = CStr(ws.Protection.AllowEditRanges.Count)
            If Err.Number <> 0 Then
                editRanges = "N/A"
                Err.Clear
            End If
            On Error GoTo CleanUp
            rpt.Cells(row, 4) = editRanges

        Else
            rpt.Cells(row, 2) = "No"
            rpt.Cells(row, 3) = "—"
            rpt.Cells(row, 4) = "—"
        End If

        ' Cell-lock sample check (A1)
        On Error Resume Next
        cellLocked = IIf(ws.Cells(1, 1).Locked, "Yes", "No")
        If Err.Number <> 0 Then cellLocked = "Error"
        Err.Clear
        On Error GoTo CleanUp
        rpt.Cells(row, 5) = cellLocked

        row = row + 1
    Next ws

    ' ── Step 3: Format the report ──────────────────────
    rpt.Columns("A:E").AutoFit
    rpt.Cells.EntireColumn.AutoFit

    ' Freeze header row
    rpt.Activate
    ActiveWindow.FreezePanes = False
    rpt.Range("A2").Select
    ActiveWindow.FreezePanes = True

    ' ── Step 4: Summary message ────────────────────────
    If protCount = 0 Then
        MsgBox "No protected sheets found in this workbook." & _
               vbCrLf & "Report on 'Protection-Report' sheet.", _
               vbInformation, "Protection Report"
    Else
        MsgBox protCount & " sheet(s) protected, " & _
               ThisWorkbook.Worksheets.Count - protCount & _
               " sheet(s) unprotected." & vbCrLf & _
               "Full report on 'Protection-Report' sheet.", _
               vbInformation, "Protection Report"
    End If

CleanUp:
    Application.ScreenUpdating = True

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

#How It Works

#Five columns, one read-only pass

The report sheet has five columns that tell you everything about protection across the workbook:

Column	What it tells you
Sheet Name	Every sheet in the workbook, listed in order
Protected	Yes/No — is ws.ProtectContents true?
Has Password	Yes/No — does an empty-string unlock fail?
Editable Ranges	Count of ranges users can edit even when the sheet is protected
Cells Locked	Yes/No — sample check on cell A1’s Locked property

There is no modification pass. After the macro finishes, every sheet is in the same state it was before. The report is read-only — nothing was unlocked, nothing was re-protected with a different password.

#Password detection without knowing the password

The macro can’t read passwords — Excel doesn’t expose them. But it can test whether a password exists by attempting an unlock with an empty string:

On Error Resume Next
ws.Unprotect ""
If Err.Number <> 0 Then
    hasPW = "Yes"
    Err.Clear
Else
    hasPW = "No"
    ws.Protect ""     ' Re-protect immediately
End If

If ws.Unprotect "" succeeds, there’s no password. If it fails, either the sheet has a password or Excel rejected the call for another reason (the worksheet protection dialog was open, the workbook is shared, etc.).

After testing, if the sheet was unlocked, it’s immediately re-protected with ws.Protect "" — same state, no password. The user sees no flicker because ScreenUpdating = False.

#Why A1 for the cell-lock sample

The macro checks ws.Cells(1, 1).Locked as a representative sample of whether cells are locked at the cell-format level. In most tax workpapers, all cells are locked before sheet protection is applied, or all cells are unlocked with specific ranges locked. A1 is a reasonable proxy.

cellLocked = IIf(ws.Cells(1, 1).Locked, "Yes", "No")

If A1’s Locked property doesn’t match the rest of the sheet (unlikely but possible), the report is slightly misleading. For a complete audit, you’d need to check every cell — but that’s a different macro. The sample check gives you a fast, good-enough signal.

#Editable ranges — the “I can edit C3 but not A1” answer

When a sheet is protected but some cells are still editable, it’s because the original preparer defined editable ranges (Review → Allow Edit Ranges):

editRanges = CStr(ws.Protection.AllowEditRanges.Count)

If Fixed Assets shows Protected: Yes, Editable Ranges: 2, that means two ranges are carved out for editing. The asset description column might be open while the cost basis column is locked. The count tells you the number of ranges, not which ranges — but it confirms that the sheet isn’t fully locked down.

The On Error Resume Next wrapper handles older Excel versions where AllowEditRanges might not be available.

#The report goes at the front for visibility

Set rpt = ThisWorkbook.Sheets.Add(Before:=ThisWorkbook.Sheets(1))

The report sheet is inserted as the first tab in the workbook, not buried at the end among 30 workpaper tabs. When the user opens the file or tabs back from the VBA editor, the report is the first thing they see.

#Freeze panes on the header row

rpt.Range("A2").Select
ActiveWindow.FreezePanes = True

With the header row frozen, the user can scroll through a workbook with 40 sheets and always see the column labels. This is the same pattern used in Formula Reporter and Workbook Inventory.

#Adapt It