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?

What's the text of the error message? – Tim Williams Feb 07 '12 at 22:22 — Tim Williams, Feb 07 '12 at 22:22
@Tim Good point, included in the question. – badp Feb 07 '12 at 22:25 — badp, Feb 07 '12 at 22:25

score 23 · Answer 1 · edited Oct 18 '16 at 15:08

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).

A note to add to this comprehensive answer is that, for a given range or a named range that contains a set of addresses, you will need to loop through the cells and rebuild that given range to contain all merged areas of all those cells, then you can set the locked property in one go using `NewlyBuiltRange.Locked = True` — Jalal, Nov 30 '21 at 16:24

score 4 · Answer 2 · edited Jan 25 '14 at 22:33

You cannot lock a cell that is part of a range of merged cells unless it is the upper-left corner cell. The following works for any cell or merged area of cells.

Sub LockCells()
   Dim R As Range
   ActiveSheet.Unprotect
   Cells.Locked = False
   For Each R In Range("A1", Cells.SpecialCells(xlCellTypeLastCell).Address)
      If R.MergeArea.Range("A1").Address = R.Address And R.HasFormula Or IsText(R) Then
         R.MergeArea.Locked = True
      End If
   Next
   ActiveSheet.Protect
End Sub

Function IsText(What) As Boolean
   IsText = False
   On Error Resume Next
   IsText = (CDbl(What) <> What)
   If Err.Number Then IsText = True
End Function

badp · Answer 3 · 2012-02-07T22:26:39.733

3

[the sheet] gets reprotected automatically on selection change

The sheet, for some reason, reprotected itself in the middle of the method. Unprotecting it again immediately before to the setting of the Lockedproperty fixed my issue.

I actually had multiple ranges that I needed to lock in a row, and had to unprotect the sheet before every single property change.

edited Feb 07 '12 at 22:26

answered Feb 07 '12 at 22:20

badp

11,409
3
61
89

score 0 · Answer 4 · edited May 16 '21 at 02:21

I had the same problem and tried to hide manually the cells, and receive the following warning: Cannot shift objects off sheet.

My problem was some comments in cells of the same sheet. After remove all the comments of the sheet, the code runs.

My code find the row using match case in a Column used only for this (because I need to insert some rows and the Column "B" is my reference to identify the row).

My Code :

Rows((WorksheetFunction.Match(1, Range("B:B"), 0)) - 1 & ":" & (WorksheetFunction.Match(13, Range("B:B"), 0)) + 15).EntireRow.Hidden = True

score 0 · Answer 5 · answered Jul 19 '19 at 14:15

0

Ran into a similar issue. Turned out the user had filtered data and then saved it and protection can't be changed on a filtered data set without turning the filters off.

answered Jul 19 '19 at 14:15

Ammar Naseer

101
1

Error 1004 when setting Range.Locked

5 Answers5

Linked