🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA Protect Sheet in Excel — Lock a Worksheet Without Locking Out Your Inputs

|

VBA Protect Sheet in Excel — Lock a Worksheet Without Locking Out Your Inputs

TL;DR — Worksheet.Protect does not decide which cells are locked. It is a master switch that turns on the Locked label already sitting on every cell — and every cell is Locked = True by default. So protecting a sheet with no preparation freezes everything, including the cells your users are supposed to fill in. The real workflow is inverted: first unlock the input cells, then protect the sheet. Protection also blocks your own macros unless you pass UserInterfaceOnly:=True, and that flag is not saved with the file — you re-apply it on every Workbook_Open.

Sub LockDownForm()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Form")

    ws.Cells.Locked = True                 ' everything starts locked anyway - be explicit
    ws.Range("C4:C12").Locked = False      ' unlock ONLY the input cells
    ws.Protect Password:="ac", AllowFiltering:=True   ' now flip the master switch on
End Sub

Protect is the code behind Review ▸ Protect Sheet. It is one of the most common finishing lines in a form- or report-building macro, and also one of the most misread — because people expect it to lock "the cells I care about" when in fact it locks every cell that carries a Locked label, and Excel puts that label on all of them the moment a sheet is born. Once you see Protect as a switch that enforces a decision the cells already carry, rather than as the thing that makes the decision, every surprising behavior falls into place.

What you'll learn

  • The mental model — Protect is a switch that enforces the Locked label, it does not choose it
  • The one rule that prevents most bugs — unlock the inputs first, then protect
  • Why Protect with no arguments also blocks filtering, sorting and formatting
  • Why a protected sheet breaks your own macros, and what UserInterfaceOnly:=True really does
  • Why that flag vanishes on save, and where to re-apply it
  • Why the password is an accident guard, not real security

The mental model: a switch, not a decision

Think of protection as two separate things that people constantly collapse into one. The decision — "which cells may a user edit?" — lives on each cell as its Locked property. The enforcement — "start honoring those decisions now" — is Worksheet.Protect. The switch does not read your intentions; it reads the Locked flag that is already on every cell, and by default that flag is True everywhere.

That single fact explains the number-one complaint about sheet protection, which is the exact opposite of what beginners expect. People assume an unprepared ws.Protect locks nothing, or locks "the important cells." It locks all of them, because all of them were born locked. So the surprise is never "my protection did not work" — it is "my protection worked too well and now nobody can type anything."

The rule that matters most: unlock the inputs first, then protect

Because every cell defaults to Locked = True, the correct pattern is not "lock the cells I want to protect." It is "unlock the few cells I want to leave open, then protect the rest." You mark the exceptions, not the targets:

ws.Cells.Locked = True              ' the default, stated for the next reader
ws.Range("C4:C12").Locked = False   ' the input column - the ONLY editable cells
ws.Protect Password:="ac"           ' enforce it

Do it in the other order and you get the classic failure. Call ws.Protect on a fresh sheet and then try ws.Range("C4").Locked = False, and the second line raises run-time error 1004 — you cannot change a cell's Locked state while the sheet is protected. The Locked labels have to be set while the sheet is unprotected; Protect is always the last step, after the map of who-can-edit-what is finished. This is the whole cluster in one sentence: Locked is the map, Protect turns on enforcement.

Protect with no arguments locks more than you think

ws.Protect looks like a plain on/off, but it takes a long list of arguments, and its defaults are restrictive. With no arguments, a protected sheet also stops users from sorting, filtering, formatting, and inserting or deleting rows and columns — even in cells that are unlocked. This is the source of the second-most-common complaint: "I protected the sheet and now the AutoFilter dropdowns are dead."

The arguments are a permissions menu. Turn back on exactly what the sheet needs:

ws.Protect Password:="ac", _
    AllowFiltering:=True, _
    AllowSorting:=True, _
    AllowFormattingCells:=True

There is a catch worth knowing: AllowFiltering:=True lets users use AutoFilter dropdowns that already exist, but not create new ones — so apply the AutoFilter before you protect. The judgment here is to protect with intent: start from "everything is blocked" and switch on the specific interactions this sheet is meant to allow, rather than shipping the restrictive defaults and fielding the complaints.

The trap that bites every macro author: protection blocks your own code

Here is the one that turns a working macro into run-time error 1004 the day after you add protection. A protected sheet does not distinguish between a user typing and your VBA writing — it blocks both. So a routine that was happily doing ws.Range("A1").Value = 42 starts failing the moment the sheet is protected, because your own code is now treated like an intruder.

The fix is the UserInterfaceOnly argument:

ws.Protect Password:="ac", UserInterfaceOnly:=True

With UserInterfaceOnly:=True, the sheet is protected against the user interface — clicks and typing — but your macros can still change it freely, with no Unprotect/Protect dance around every write. It is the cleanest way to keep a sheet locked to people while your code keeps working. But there is a sting in the tail, and it is the next section.

Why UserInterfaceOnly vanishes when you save

UserInterfaceOnly:=True is not saved with the workbook. When the file is closed and reopened, the sheet comes back fully protected — against your macros too — as if you had never passed the flag. The protection persists; the "but let my code through" part does not. This is why a macro works all session and then throws 1004 the next morning, mystifying everyone.

The fix is to re-apply protection with the flag every time the workbook opens:

' In the ThisWorkbook module
Private Sub Workbook_Open()
    Worksheets("Form").Protect Password:="ac", UserInterfaceOnly:=True
End Sub

This re-establishes the "protected to users, open to code" state on load without unprotecting anything or disturbing the Locked map. Treat it as a required companion to any UserInterfaceOnly protection: if you rely on the flag at all, you rely on Workbook_Open to restore it. See Workbook_Open for the event mechanics.

The password is a guard rail, not a lock

The Password argument feels like security, and it is worth being honest about what it is not. Sheet protection passwords use weak, well-documented encryption; they are trivially removed by any number of tools and can be reset outside Excel entirely. Treat the password as a guard rail — it stops a colleague from casually clicking into your formulas or overwriting a template — not as a vault for anything confidential. If the data genuinely must be secret, sheet protection is the wrong tool; keep it out of the file. And keep the password somewhere safe: VBA can Protect and Unprotect with a password you know, but it cannot recover one you have forgotten.

How ExcelMaster helps

Protection is a two-step system that reads backwards, so it fails in ways that never raise an error at the moment you get it wrong — you lock out every input because the cells were locked by default, you ship a "read-only" sheet whose filters are dead, or your own macro throws 1004 the morning after because UserInterfaceOnly was lost on save.

ExcelMaster lets you say what you actually want — "lock this template but let people fill in the yellow cells and still use the filters" — and it writes the unlock-then-protect steps in the right order, switches on the AllowXxx permissions the sheet needs, adds UserInterfaceOnly:=True with a Workbook_Open to keep it alive across saves, and tells you plainly that the password guards against accidents, not against a determined reader. You keep the workbook and the code.

Frequently asked questions

Why can nobody type anything after I protect the sheet in VBA?

Because every cell is Locked = True by default, so ws.Protect locks the entire sheet. Protection does not pick "the important cells" — it enforces the Locked label that is already on all of them. Set Locked = False on your input range before protecting: ws.Range("C4:C12").Locked = False, then ws.Protect. Unlock the exceptions, then flip the switch.

How do I protect a sheet with a password in VBA?

Pass the Password argument: ws.Protect Password:="ac". To unprotect, supply the same password: ws.Unprotect Password:="ac". Keep the password in your code or a safe place — VBA cannot recover a forgotten one. And treat it as an accident guard, not security: sheet-protection passwords are trivially removable and should never be trusted to keep confidential data hidden.

Why does my macro get error 1004 on a protected sheet?

A protected sheet blocks your VBA writes exactly as it blocks a user. Protect with UserInterfaceOnly:=True so the sheet stays locked to people while your code can still change it. Note that this flag is not saved with the workbook, so re-apply it in a Workbook_Open event — Worksheets("Form").Protect Password:="ac", UserInterfaceOnly:=True — or the macro will fail after the file is reopened.

How do I keep filtering and sorting working on a protected sheet?

By default Protect blocks them. Turn them back on with the arguments: ws.Protect AllowFiltering:=True, AllowSorting:=True. AllowFiltering:=True lets users operate AutoFilter dropdowns that already exist, but not create new ones, so apply the AutoFilter before you protect. Add AllowFormattingCells:=True and the other AllowXxx flags for any interaction the sheet still needs.

What is the difference between protecting a sheet and protecting the workbook?

Worksheet.Protect locks cells on one sheet from being edited. Workbook.Protect locks the workbook structure — it stops sheets from being added, deleted, renamed, moved or hidden — and does nothing to cell contents. They solve different problems: sheet protection guards data entry, workbook protection guards the layout of tabs. You often use both on a finished template.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-11.

Related guides: VBA Unprotect · VBA Lock Cells · VBA Workbook_Open · VBA AutoFilter · VBA Range