Formula Cell Protector: Lock Every Calculation, Unlock Every Input — One Click

TL;DR: Sending a workpaper to a client means locking every formula so the recipient can change inputs but can’t break your calculations. Doing this manually — select all formula cells, Format Cells → Protection → Lock, then repeat for input cells → Unlock — on a 20-tab workbook takes 40 minutes. This macro does it in one pass: lock every formula cell, unlock every input cell, protect every sheet. One InputBox for the password. Two seconds.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook with a mix of formula cells (calculations) and non-formula cells (inputs, labels, headers) — any tax model, workpaper, or schedule
• That’s it. The macro handles lock/unlock/protect automatically.

What the macro does:

• First pass: unlocks every cell on every visible sheet
• Second pass: locks only formula cells using SpecialCells(xlCellTypeFormulas)
• Final pass: protects each sheet with the password you provide (or no password)
• Reports: how many formula cells locked, how many input cells remained unlocked, how many sheets protected

What the macro does NOT do:

• It does not modify cell values, formulas, or formatting. Only the Locked property and sheet protection state are changed.
• It does not process hidden or very-hidden sheets — those are skipped.
• It does not re-protect sheets that were already protected. Those are skipped with a note so you can handle them manually.

Limitations:

• Works on ThisWorkbook — the workbook containing the macro. Store in your Personal Macro Workbook (PERSONAL.XLSB) to protect any open file.
• Sheets that are already protected are skipped. The macro cannot unlock them without knowing the existing password. Unlock manually first, then run.
• The AllowFormattingCells:=True parameter is enabled by default so the recipient can still change fonts, colors, and column widths. See the Adapt It section to restrict this.
• If a sheet has no formula cells, all cells remain unlocked. This is correct behavior (no formulas to protect) but the sheet is still protected — the recipient sees the protection but nothing is locked.

Related macros: Run Protection Reporter first to audit current protection states. Use Sheet Lock / Unlock for simple lock-all or unlock-all without formula-aware cell locking.

#The Macro

Option Explicit

Sub FormulaCellProtector()
    ' ── Formula Cell Protector ─────────────────────────
    ' Locks every formula cell, unlocks every non-formula
    ' cell, then protects every visible sheet. The
    ' result: recipients can edit inputs but can't touch
    ' your calculations.
    '
    ' Use before sending workpapers to clients, before
    ' partner review, or any time you need a model that's
    ' safe to share without exposing formula logic.
    ' ────────────────────────────────────────────────────

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

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet
    Dim formulaCells As Range
    Dim pwd As String
    Dim totalFormulas As Long
    Dim totalInputs As Long
    Dim protectedCount As Long
    Dim skippedCount As Long
    Dim skippedList As String
    Dim wsFormulas As Long
    Dim wsInputs As Long

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

    ' ── Get the password ────────────────────────────────
    pwd = InputBox("Enter a password to protect sheets" & _
                   vbCrLf & "(leave blank for no password):", _
                   "Formula Cell Protector")

    ' ── Confirm before proceeding ──────────────────────
    If MsgBox("This will:" & vbCrLf & _
              "  • UNLOCK all input cells" & vbCrLf & _
              "  • LOCK all formula cells" & vbCrLf & _
              "  • PROTECT every visible sheet" & vbCrLf & _
              vbCrLf & "Sheets already protected will be skipped." & _
              vbCrLf & "Continue?", _
              vbYesNo + vbQuestion, _
              "Formula Cell Protector") = vbNo Then GoTo CleanUp

    totalFormulas = 0
    totalInputs = 0
    protectedCount = 0
    skippedCount = 0
    skippedList = ""

    ' ── Process each visible sheet ─────────────────────
    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible <> xlSheetVisible Then GoTo NextSheet

        ' Check if already protected
        If ws.ProtectContents Then
            skippedCount = skippedCount + 1
            skippedList = skippedList & "  • " & ws.Name & _
                          " (already protected)" & vbCrLf
            GoTo NextSheet
        End If

        ' ── Step 1: Unlock ALL cells ──────────────────
        ws.Cells.Locked = False

        ' ── Step 2: Lock only formula cells ───────────
        On Error Resume Next
        Set formulaCells = Nothing
        Set formulaCells = ws.Cells.SpecialCells(xlCellTypeFormulas)
        On Error GoTo CleanUp

        wsFormulas = 0
        If Not formulaCells Is Nothing Then
            formulaCells.Locked = True
            wsFormulas = formulaCells.Cells.Count
            totalFormulas = totalFormulas + wsFormulas
        End If

        ' Count non-formula, non-blank cells as inputs
        ' (approximated as used range cells minus formula cells)
        On Error Resume Next
        wsInputs = ws.UsedRange.Cells.Count - wsFormulas
        On Error GoTo CleanUp
        If wsInputs < 0 Then wsInputs = 0
        totalInputs = totalInputs + wsInputs

        ' ── Step 3: Protect the sheet ─────────────────
        ws.Protect Password:=pwd, _
                   UserInterfaceOnly:=True, _
                   AllowFormattingCells:=True
        protectedCount = protectedCount + 1

NextSheet:
    Next ws

    ' ── Report results ──────────────────────────────────
    Dim msg As String
    msg = "PROTECTION COMPLETE" & vbCrLf & vbCrLf
    msg = msg & "Sheets protected: " & protectedCount & vbCrLf
    msg = msg & "Formula cells LOCKED: " & totalFormulas & vbCrLf
    msg = msg & "Input cells UNLOCKED: " & totalInputs & vbCrLf

    If pwd = "" Then
        msg = msg & vbCrLf & "No password was set."
    End If

    If skippedCount > 0 Then
        msg = msg & vbCrLf & vbCrLf & _
              skippedCount & " sheet(s) SKIPPED" & _
              " (already protected):" & vbCrLf & skippedList
    End If

    MsgBox msg, vbInformation, "Formula Cell Protector"

CleanUp:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

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

#How It Works

#The three-step lock pattern

The core of the macro is a three-step sequence applied to each visible sheet:

1. Unlock every cell — ws.Cells.Locked = False
2. Lock formula cells — find them with SpecialCells, set .Locked = True
3. Protect the sheet — ws.Protect with the user’s password

The order matters. You must unlock everything first because Excel’s default state is “all cells locked.” If you only lock formula cells without first unlocking the rest, every cell — formulas, inputs, labels, blanks — ends up locked. The recipient can’t click anywhere. By unlocking everything first, then selectively re-locking only formulas, you get exactly the result you want: formulas are locked, everything else is open.

#Why SpecialCells is faster than looping

Finding formula cells by looping every cell on every sheet would take forever on a 20-sheet workbook. SpecialCells(xlCellTypeFormulas) does it in a single call. It returns a Range object containing every formula cell on the sheet, contiguous or not. Setting .Locked = True on that range applies the property to all of them at once:

Set formulaCells = ws.Cells.SpecialCells(xlCellTypeFormulas)
If Not formulaCells Is Nothing Then
    formulaCells.Locked = True
End If

The On Error Resume Next wrapper is critical. If a sheet has zero formula cells (e.g., a cover page or a data-only import), SpecialCells raises an error — there are no matching cells. The macro catches that, skips the lock step, and moves on. The sheet still gets protected (protection without locked cells means the user can edit anything — but at least they can’t rename or delete the sheet).

#UserInterfaceOnly keeps your other macros working

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

UserInterfaceOnly:=True means the protection only applies to human interaction. VBA code can still modify cells, insert rows, and apply formatting on protected sheets. This is deliberate. If you have a reporting macro or a data-refresh macro that writes to protected sheets, it still works. The recipient can’t accidentally break formulas, but your automation doesn’t break either.

AllowFormattingCells:=True lets the recipient change fonts, colors, and number formats. Most tax preparers want to format numbers as they work — bold the totals, color-code variances, adjust column widths. This parameter lets them do that without touching the formulas. If you need a stricter lockdown, see the Adapt It section.

#Already-protected sheets are skipped, not forced

If ws.ProtectContents Then
    skippedCount = skippedCount + 1
    skippedList = skippedList & "  • " & ws.Name & _
                  " (already protected)" & vbCrLf
    GoTo NextSheet
End If

If the prior preparer already protected Fixed Assets with their own password, the macro can’t modify the cell-level lock state — the sheet won’t accept changes while protected. Instead of trying to brute-force it (which would require knowing the password), the macro skips it and reports the sheet name. You can unlock those sheets manually (run Sheet Lock / Unlock with the right password), then run this macro again.

#The confirmation message is the safety net

Before the macro touches anything, it shows exactly what it will do:

"This will:" & vbCrLf & _
"  • UNLOCK all input cells" & vbCrLf & _
"  • LOCK all formula cells" & vbCrLf & _
"  • PROTECT every visible sheet"

This is important because the macro modifies cell properties on every sheet. The confirmation gives you a chance to cancel if you realize you left the wrong workbook open, or you forgot to unlock a sheet manually first. Once confirmed, the macro runs in a single pass — there’s no Undo for cell-level Locked property changes applied by VBA. (Sheet protection can be removed manually, but the per-cell lock state changes persist.)

#The report tells you exactly what changed

A 20-sheet workpaper with 2,847 formulas and 3,120 inputs produces a MsgBox like:

PROTECTION COMPLETE

Sheets protected: 18
Formula cells LOCKED: 2,847
Input cells UNLOCKED: 3,120

2 sheet(s) SKIPPED (already protected):
  • Fixed Assets (already protected)
  • Tax Calc (already protected)

The counts let you spot-check. If “Formula cells LOCKED” shows 0, something is wrong — maybe the workbook format doesn’t have formulas, or you pasted values already. If “Input cells UNLOCKED” is much lower than expected, you may have more formulas than you realized. The skipped sheet list makes it easy to find sheets that need manual attention.

#Adapt It