excel - Error 1004 when setting Range.Locked

Question

I get the catch-all runtime error 1004, "unable to set the Locked property of the Range class" when, well, trying to set the Locked property of a Range object. The code looks like this:

that_goddamn_sheet.Unprotect

; Determine if we should proceed

that_goddamn_range.Locked = True

; Do more stuff

that_goddamn_sheet.Protect

When I set a breakpoint on the line and try to query the value of that_goddamn_range.Locked from the Immediate window, this works without problem, but setting it fails.

If, however, I run that_goddamn_range.Select, break the macro, unprotect the sheet (it gets reprotected automatically on selection change) then right click, pick Properties, switch to the Protection tab and tick Locked then confirm, things work okay however.

What could possibly be going wrong?

Answer 1

Here’s a complete explanation:

Typically, there are two causes of this error: trying to change a cell’s Locked property on a protected sheet and/or trying to change the Locked property of a single cell in a merged range.

In the first case, you can either unlock the sheet or set UserInterfaceOnly protection for it, which is highly recommended since you then don’t have to mess with repeatedly unlocking/locking it.

With regard to merged cells, you can’t lock a single cell that is part of a merged range but there are reasonable options:

• If you are using cell-address notation to reference the cell/range,

  Range("A1").Locked = True

  then reference the whole merged range instead:

  Range("A1:A3").Locked = True 'where "A1:A3" is the entire merged range

• If you are using a named range for a set of merged cells, by default it will be defined to reference the first of the merged cells only. You can either edit its definition to include the entire merged range or use its MergeArea property to reference its associated merged range:

  Range(“SomeNamedRange”).MergeArea.Locked = True

  But note that you can’t do both since the MergeArea property is, apparently, undefined for a range that is not a strict subset of a larger merged area!

• Of course, you can always unmerge the merged range before setting the Locked property of an included cell and then re-merge it afterward, but I have never seen a situation in which one of the above two solutions wasn’t sufficient (and much cleaner).