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

VBA Lock Cells in Excel — The Locked Property That Does Nothing on Its Own

|

VBA Lock Cells in Excel — The Locked Property That Does Nothing on Its Own

TL;DR — Range.Locked is a label, not a lock. Setting Locked = True changes nothing you can see or feel; the cell only becomes uneditable once the sheet is protected, at which point Excel reads every cell label and freezes the ones marked Locked. That is why the classic complaint — "I set Locked = True but users can still edit the cell" — is not a bug: the sheet was never protected. And because every cell starts Locked = True, the real workflow is inverted: you unlock the input cells (Locked = False) and then protect, letting everything else inherit the default lock.

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

    ws.Cells.Locked = True                 ' every cell already is - being explicit
    ws.Range("C4:C12").Locked = False      ' the inputs are the exceptions
    ws.Range("C4:C12").FormulaHidden = False
    ws.Protect Password:="ac"              ' NOW the labels start to mean something
End Sub

Locked is the checkbox in Format Cells ▸ Protection, and it is the single most misunderstood property in Excel automation — because setting it appears to do nothing. You run cell.Locked = True, you click the cell, and you can still type in it. Everything is working exactly as designed; you have just discovered that Locked is inert on its own. Once you internalize that Locked is a note to the protection system rather than an act of locking, the whole feature becomes predictable.

What you'll learn

  • The mental model — Locked is a label the protection switch reads, not a lock in itself
  • The one rule that explains the number-one bug — Locked does nothing until you protect the sheet
  • Why every cell starts locked, and the inverted "unlock the inputs" workflow that follows
  • Why Locked reads back Null on a mixed selection, and how to test for it
  • FormulaHidden — the companion label that hides a formula from the formula bar
  • Why you must set Locked while the sheet is unprotected

The mental model: a label, not a lock

Picture Locked as a sticky note on each cell that reads "freeze me when protection is on." Writing the note does not freeze anything; it just records an intention. The actual freezing happens later, and by someone else — Worksheet.Protect — which walks the sheet, reads each note, and enforces it. Two cells can carry identical Locked labels and behave completely differently, purely depending on whether their sheet is protected.

This is why Locked and Protect are a pair that must be understood together. Locked is the map of who-may-edit-what; Protect is the switch that starts honoring the map. Neither does the job alone: a map nobody enforces changes nothing, and a switch with no map just locks everything (because the default map marks every cell locked).

The rule that explains the number-one bug: Locked is inert until you protect

The most-searched frustration behind this topic is "I set the cell to Locked = True but the user can still edit it." It is never a bug in Locked. It means the sheet is not protected, so nothing is enforcing the label. The Locked property has no effect whatsoever on an unprotected sheet — you can set it on every cell in the workbook and change nothing about what anyone can type.

ws.Range("A1").Locked = True    ' A1 is now labeled locked...
' (sheet is not protected)      ' ...and still fully editable
ws.Protect                      ' NOW A1 is frozen

So Locked is always half of a two-step, and the second step is the one people forget. If a cell must resist editing, setting Locked = True is necessary but not sufficient — the sheet has to be protected for the label to bite. Any time locking "does not work," the first thing to check is whether ws.ProtectContents is even True.

Every cell starts locked — so unlock the exceptions

Here is the fact that flips the whole workflow: every cell in a new sheet is already Locked = True. That means "locking the cells I want to protect" is almost never the right move — they are locked already. The correct pattern is the inverse: leave the default in place and unlock the handful of cells you want people to edit.

ws.Cells.Locked = True              ' the default; state it for the next reader
ws.Range("C4:C12").Locked = False   ' unlock the inputs - the exceptions
ws.Protect Password:="ac"           ' everything else inherits the lock

Think in terms of "which cells are the inputs?" and unlock those; everything else — labels, formulas, headings — keeps the default lock and is frozen the moment you protect. This scales far better than trying to enumerate every cell that should be read-only, and it is why well-built templates unlock a small, obvious input region and lock the rest by omission.

The Null trap: reading Locked on a mixed selection

Locked reads back cleanly only when a range is uniform. If a range contains both locked and unlocked cells, reading .Locked returns Null, not True or False. So a naive test blows up:

If ws.Range("A1:A10").Locked Then      ' run-time error if the range is mixed (Null)

When A1:A10 has some locked and some unlocked cells, .Locked is Null, and If Null Then raises "Invalid use of Null." Guard it with IsNull when a range might be mixed:

Dim state As Variant
state = ws.Range("A1:A10").Locked
If IsNull(state) Then
    ' mixed - decide per cell
ElseIf state Then
    ' all locked
Else
    ' all unlocked
End If

This is the same three-state behavior you see with WrapText and other cell properties: uniform ranges give a Boolean, mixed ranges give Null. Read one cell at a time when you need certainty.

FormulaHidden: lock the cell, hide the recipe

Locked has a companion label, FormulaHidden, and together they cover the two things you usually want from a delivered model: the user cannot change the formula, and cannot even see it. Like Locked, FormulaHidden does nothing until the sheet is protected; once it is, a cell with FormulaHidden = True shows its result in the grid but a blank formula bar when selected.

ws.Range("D4:D100").Locked = True
ws.Range("D4:D100").FormulaHidden = True   ' hide the calculation from the formula bar
ws.Protect Password:="ac"

Use it when you are shipping a calculation as a black box — a pricing model, a scoring formula — and you want people to trust the number without lifting the hood. As always, it is a convenience and IP gesture, not security: like protection passwords, it is easily defeated. But for keeping a formula out of casual sight it is exactly the right label.

Why you must set Locked while the sheet is unprotected

The Locked map has to be drawn while the sheet is open. Attempting cell.Locked = False on a protected sheet raises run-time error 1004 — you cannot rewrite the map while the switch that enforces it is on. So the order is fixed and non-negotiable: Unprotect (if needed), set every Locked and FormulaHidden label, then Protect. If a macro needs to change which cells are editable at run time, it must unprotect first, adjust the labels, and re-protect — you can never edit the labels through an active lock.

How ExcelMaster helps

Locked fails silently in the most confusing way possible: it does exactly nothing, with no error, until the sheet is protected — so people set it, watch users edit "locked" cells anyway, and assume the property is broken. Then the inverse workflow trips them up, or a mixed-range read throws "Invalid use of Null," or they try to relabel cells through an active protection and hit 1004.

ExcelMaster lets you say the goal — "let people edit only the yellow input cells and hide the formulas" — and it sets Locked and FormulaHidden in the right order, unlocks the inputs rather than trying to lock everything else, pairs the labels with the Protect call that actually enforces them, and guards mixed-range reads against Null. You keep the workbook and the code.

Frequently asked questions

Why can users still edit a cell after I set Locked to True in VBA?

Because Locked does nothing until the sheet is protected. It is only a label the protection system reads — an unprotected sheet ignores it entirely. Set the cells Locked = True (or leave the default), then call ws.Protect. If editing is still possible after that, confirm the sheet is actually protected with ws.ProtectContents.

How do I lock only some cells and leave the rest editable?

Use the inverted workflow. Every cell starts Locked = True, so unlock the exceptions rather than locking the targets: ws.Cells.Locked = True then ws.Range("C4:C12").Locked = False for the input cells, then ws.Protect. Everything you did not unlock inherits the default lock and is frozen once the sheet is protected.

Why does reading the Locked property give an error?

Because the range is mixed. Locked returns Null when a range contains both locked and unlocked cells, and If Range.Locked Then on a Null raises "Invalid use of Null." Test with IsNull first, or read one cell at a time: a uniform range returns a Boolean, a mixed one returns Null.

What is the difference between Locked and FormulaHidden?

Locked stops a cell being edited once the sheet is protected; FormulaHidden stops its formula being seen in the formula bar once the sheet is protected. Both are labels that only take effect under protection. Use them together to ship a calculation the user cannot change or inspect — lock the cell and hide the formula, then protect.

Can I change a cell's Locked state while the sheet is protected?

No. Setting Locked (or FormulaHidden) on a protected sheet raises run-time error 1004. You must draw the map while enforcement is off: Unprotect if needed, set the Locked labels, then Protect. A macro that changes editability at run time has to unprotect, adjust, and re-protect.

Tested in

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

Related guides: VBA Protect Sheet · VBA Unprotect · VBA Wrap Text · VBA Range · VBA Formula