Wauvel

Excel like a finance pro.

← All VBA snippets

Lock the sheet but let your macro keep writing

UserInterfaceOnly — the one flag that lets code write through protection, and that nobody knows exists.

1What it does

Protecting a model stops people typing over its formulas, and it also stops your own macros writing to it — so the usual workaround is to unprotect at the start of every macro and protect again at the end, which leaves the sheet unprotected forever if anything fails in between. `UserInterfaceOnly:=True` is the better answer: it protects the sheet against the user while leaving VBA free to write. The catch is that the setting isn't saved with the file, so it has to be reapplied each time the workbook opens — which is exactly what Workbook_Open is for.

2The code

' In ThisWorkbook: protection against the USER only, reapplied on every open
' because UserInterfaceOnly is not saved with the file.
Private Sub Workbook_Open()
    Dim ws As Worksheet
    For Each ws In Me.Worksheets
        ws.Protect Password:="model", _
                   UserInterfaceOnly:=True, _
                   AllowFormattingColumns:=True, _
                   AllowFiltering:=True
    Next ws
End Sub

' Any macro can now write to a protected sheet with no unprotect/reprotect.
Sub PostActuals()
    With ThisWorkbook.Worksheets("Model")
        .Range("C5").Value = 486000        ' locked cell, protected sheet — fine
        .Range("C6").Value = 281100
    End With
End Sub

' Before protecting, UNLOCK the input cells so users can still type in them.
Sub PrepareInputs()
    With ThisWorkbook.Worksheets("Model")
        .Unprotect Password:="model"
        .Cells.Locked = True
        .Range("B4:B12").Locked = False    ' the assumption block stays editable
    End With
End Sub

Paste into the Visual Basic Editor (Alt + F11 → Insert → Module), then run and save as macro-enabled (.xlsm).

New to macros? Set up in 5 minutes▾
  1. 1

    Don't see the Developer tab in the ribbon?

    You don't strictly need it — Alt + F11 opens the editor directly — but it makes running macros easier.
    • Windows: File → Options → Customize Ribbon → tick Developer in the right-hand list → OK.
    • Mac: Excel → Preferences → Ribbon & Toolbar → tick Developer → Save.
  2. 2

    Paste in the code

    Press Alt + F11 to open the Visual Basic editor, then Insert → Module and paste the snippet's code into the blank window. Close it with Alt + Q.
  3. 3

    Run it

    Press Alt + F8, pick the macro's name, and click Run — that's it. (Pasted a custom function instead? Just type it into a cell like any built-in: =GrossMargin(B2, B3).)
  4. 4

    Keep the macro — save as .xlsm

    File → Save As → Excel Macro-Enabled Workbook (.xlsm). A plain .xlsx silently drops the code when you save.
  5. 5

    Macros blocked?

    Click Enable Content on the yellow bar. If you downloaded the file, you may first need to right-click it → Properties → tick Unblock → OK, then reopen.

Heads up: macros can't be undone with Ctrl + Z — save a copy before running one that changes your workbook.

3When you use it

  • Protect a model's formulas while macros still post actuals into it.
  • Hand out a template where only the input cells can be edited.
  • Remove the unprotect/reprotect dance from every macro in a workbook.

4See it in action

A simulation — press Run to perform what the macro does to a sample workbook.

Model sheet after the run
C5 revenue—
C6 COGS—
Sheet protectionON

5Pitfalls

The unprotect-then-reprotect pattern leaves the sheet unprotected whenever a macro fails mid-way.

Fix: UserInterfaceOnly:=True, so macros never need to unprotect at all.

It works today and fails tomorrow with "the cell is protected".

Fix: UserInterfaceOnly isn't saved with the file. Reapply it in Workbook_Open every time.

Users can't type in the inputs either.

Fix: Every cell is Locked by default. Unlock the input range BEFORE protecting.

The password sits in plain text in the code.

Fix: Sheet protection is about accidents, not security — it's trivially removable. Lock the VBA project too if the code shouldn't be casually read.

6No-code alternatives

  • Review → Protect Sheet by hand — Right when no macro writes to the sheet. Unlock the inputs first either way. See the tip.
  • Workbook structure protection — Stops sheets being added, deleted or renamed, which is a different job from protecting cells.

Rather not write macros? Wauvel's free tools generate branded, formula-driven Excel for you — no VBA required.

Learn the moves here — or let Wauvel run them on your numbers.

Meet your AI CFO →

One CFO-grade Excel tip a week

A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.