Sheet Lock / Unlock: Protect Every Tab in One Click Before Filing

TL;DR: Finalizing a workpaper means protecting every sheet — one at a time, right-click, Protect Sheet, type password, confirm password, repeat. This macro asks for the password once and locks or unlocks every visible sheet with one MsgBox click. A 15-minute chore becomes 3 seconds.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook with sheets you want to lock or unlock
• That’s it. The macro prompts you for everything at runtime.

Limitations:

• Only processes visible sheets. Hidden and very-hidden sheets are skipped. If you need to lock hidden sheets, unhide them first (see the Unhide All Sheets macro) or modify the visibility check in the code.
• If a sheet already has a different password, the unlock will fail. The macro reports which sheets were skipped so you can handle them manually.
• Uses UserInterfaceOnly:=True so macros can still modify protected sheets (formatting, data writes from other macros). If you need full lockout, change this in the Adapt It section.
• Works on ThisWorkbook — the workbook containing the macro. Store in your Personal Macro Workbook to run on any open file.

#The Macro

Option Explicit

Sub SheetLockUnlock()
    ' ── Sheet Lock / Unlock ────────────────────────────
    ' Locks or unlocks every visible sheet in the
    ' workbook with one password. Prompts for the
    ' password (blank = no password), then asks whether
    ' to lock or unlock all sheets. Reports per-sheet
    ' results including sheets that couldn't be
    ' unlocked due to a different password.
    '
    ' Use when finalizing workpapers before filing,
    ' or when unlocking for review changes.
    ' ────────────────────────────────────────────────────

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

    ' ── Variables ──────────────────────────────────────
    Dim pwd As String
    Dim ws As Worksheet
    Dim action As VbMsgBoxResult
    Dim locked As Long
    Dim unlocked As Long
    Dim skipped As Long
    Dim skippedList As String
    Dim verb As String

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

    ' ── Get password ────────────────────────────────────
    pwd = InputBox("Password (leave blank for no password):", _
                   "Sheet Lock / Unlock")

    ' ── Choose lock or unlock ──────────────────────────
    action = MsgBox("Lock or unlock all visible sheets?" & _
                    vbCrLf & vbCrLf & _
                    "  Yes  = Lock All" & vbCrLf & _
                    "  No   = Unlock All", _
                    vbYesNoCancel + vbQuestion, _
                    "Sheet Lock / Unlock")
    If action = vbCancel Then GoTo CleanUp

    verb = IIf(action = vbYes, "Locked", "Unlocked")
    locked = 0
    unlocked = 0
    skipped = 0
    skippedList = ""

    ' ── Process each sheet ─────────────────────────────
    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then
            If action = vbYes Then
                ' ── Lock ───────────────────────────────
                ws.Protect Password:=pwd, _
                           UserInterfaceOnly:=True
                locked = locked + 1
            Else
                ' ── Unlock ─────────────────────────────
                If ws.ProtectContents Then
                    On Error Resume Next
                    ws.Unprotect Password:=pwd
                    If Err.Number = 0 Then
                        unlocked = unlocked + 1
                    Else
                        skipped = skipped + 1
                        skippedList = skippedList & _
                            "  • " & ws.Name & _
                            " (wrong password?)" & vbCrLf
                        Err.Clear
                    End If
                    On Error GoTo CleanUp
                Else
                    unlocked = unlocked + 1
                End If
            End If
        End If
    Next ws

    ' ── Report results ──────────────────────────────────
    Dim msg As String
    If action = vbYes Then
        msg = locked & " sheet(s) locked."
        If pwd = "" Then
            msg = msg & vbCrLf & "No password was set."
        End If
    Else
        msg = unlocked & " sheet(s) unlocked."
        If skipped > 0 Then
            msg = msg & vbCrLf & vbCrLf & _
                  skipped & " sheet(s) skipped:" & _
                  vbCrLf & skippedList
        End If
    End If
    MsgBox msg, vbInformation, "Sheet Lock / Unlock"

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

#One InputBox, one MsgBox, done

The entire user interface is two dialogs. The first asks for a password — and accepting a blank value is intentional. Sometimes you want protection without a password (just preventing accidental edits). Sometimes you want a password. The macro doesn’t force you either way.

pwd = InputBox("Password (leave blank for no password):", _
               "Sheet Lock / Unlock")

The second dialog is a three-button MsgBox: Yes = lock, No = unlock, Cancel = do nothing. This is cleaner than two separate macros because the user is making one decision (“am I locking or unlocking?”) not picking one of two macros to run.

action = MsgBox("Lock or unlock all visible sheets?" & vbCrLf & vbCrLf & _
                "  Yes  = Lock All" & vbCrLf & _
                "  No   = Unlock All", _
                vbYesNoCancel + vbQuestion, _
                "Sheet Lock / Unlock")

The vbYesNoCancel gives three buttons so there’s an explicit cancel path. The vbCrLf line breaks make the two options visually distinct — no one misreads and accidentally clicks the wrong button.

#UserInterfaceOnly:=True is deliberate

ws.Protect Password:=pwd, UserInterfaceOnly:=True

This is the most important design decision in the macro. Without UserInterfaceOnly, a protected sheet blocks everything — including other VBA macros. If you have a formatting macro or a report generator that modifies cells on protected sheets, it breaks. UserInterfaceOnly:=True means:

• Blocked: Typing in cells, deleting rows, dragging formulas, any mouse/keyboard edit
• Allowed: VBA code modifying cells, formatting, inserting rows programmatically

This is the right default for a workpaper automation blog — the reader probably has other macros and doesn’t want this one to break them. If you need full protection (blocking VBA too), see the Adapt It section.

#Unlocking handles the wrong-password case

When unlocking, the macro checks ws.ProtectContents first. This Boolean property tells you whether a sheet is currently protected — no need to attempt an unlock and catch the error for every sheet.

If ws.ProtectContents Then
    On Error Resume Next
    ws.Unprotect Password:=pwd
    If Err.Number = 0 Then
        unlocked = unlocked + 1
    Else
        skipped = skipped + 1
        skippedList = skippedList & "  • " & ws.Name & _
                      " (wrong password?)" & vbCrLf
        Err.Clear
    End If
    On Error GoTo CleanUp
Else
    unlocked = unlocked + 1
End If

The On Error Resume Next is scoped to just the Unprotect call — it’s immediately followed by On Error GoTo CleanUp to restore normal error handling. This is the “dangerous operation” pattern: temporarily suppress errors for one specific line that might fail, then restore the safety net.

If the password doesn’t match, Excel raises an error. The macro catches it, logs the sheet as skipped, clears the error with Err.Clear, and moves on. The skipped sheets appear in the final MsgBox so you know exactly which ones need manual attention.

#Hidden sheets are left alone

If ws.Visible = xlSheetVisible Then

The macro only processes visible sheets. If a sheet is hidden (xlSheetHidden) or very-hidden (xlSheetVeryHidden), it’s skipped entirely. Hidden sheets are often internal reference data or macro state that was never meant to be touched by the user — locking or unlocking them would be confusing. If you need to process hidden sheets, unhide them first with the Unhide All Sheets macro.

#The message box tells the full story

A silent lock-all is dangerous for the same reason a silent find-and-replace is dangerous: you don’t know what happened. The MsgBox reports:

Locking: "14 sheet(s) locked."
Unlocking (all success): "14 sheet(s) unlocked."
Unlocking (partial): "11 sheet(s) unlocked. 3 sheet(s) skipped:
  • Sched-C — WP (wrong password?)
  • Fixed Assets (wrong password?)
  • Depreciation (wrong password?)"

The skipped sheets are listed by name so you can find them fast. The (wrong password?) note is deliberately a question — the macro can know that the password didn’t work, but it can’t know why. Maybe it’s a different password. Maybe the sheet has other protection settings (like AllowFormattingCells:=False) that the VBA Unprotect doesn’t match.

#Adapt It