vba 设置 Range.Locked 时出现错误 1004

Error 1004 when setting Range.Locked

提问by badp

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

当我尝试设置LockedRange 对象的属性时，我收到了全面的运行时错误 1004，“无法设置 Range 类的 Locked 属性” 。代码如下所示：

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.Lockedfrom the Immediate window, this works without problem, but setting it fails.

当我在行上设置断点并尝试that_goddamn_range.Locked从立即窗口查询 的值时，这没有问题，但设置失败。

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.

但是，如果我运行that_goddamn_range.Select，破坏宏，取消保护工作表（它会在选择更改时自动重新保护）然后右键单击，选择属性，切换到保护选项卡并勾选锁定然后确认，但是一切正常。

What could possibly be going wrong?

可能出什么问题了？

回答by pstraton

Here's a complete explanation:

这是一个完整的解释：

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

通常，此错误有两个原因：尝试更改受保护工作表上单元格的Locked属性和/或尝试更改合并范围中单个单元格的Locked属性。

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

在第一种情况下，您可以解锁工作表或为其设置UserInterfaceOnly保护，强烈建议您这样做，因为这样您就不必反复解锁/锁定它。

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 MergeAreaproperty to reference its associated merged range:

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

  But note that you can't do both since the MergeAreaproperty is, apparently, undefined for a range that is not a strict subsetof 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).

• 如果您使用单元格地址表示法来引用单元格/范围，

  Range("A1").Locked = True

  然后引用整个合并范围：

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

• 如果您为一组合并单元格使用命名范围，默认情况下，它将被定义为仅引用第一个合并单元格。您可以编辑其定义以包含整个合并范围，也可以使用其MergeArea属性来引用其关联的合并范围：

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

  但请注意，您不能同时执行这两项操作，因为MergeArea属性显然对于不是较大合并区域的严格子集的范围未定义！

• 当然，您始终可以在设置包含单元格的 Locked 属性之前取消合并合并范围，然后再重新合并，但我从未见过上述两种解决方案之一不够的情况（而且更干净） ）。

回答by Scot Norris

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

回答by badp

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

出于某种原因，该表在该方法的中间重新保护了自己。在设置Locked属性之前立即再次取消保护它解决了我的问题。

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

我实际上有多个范围需要连续锁定，并且必须在每次更改属性之前取消对工作表的保护。

回答by Erik Teixeira

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

我的代码在仅用于此的列中使用匹配大小写查找行（因为我需要插入一些行，而列“B”是我识别行的参考）。

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

我的代码。行((WorksheetFunction.Match(1, Range("B:B"), 0)) - 1 & ":" & (WorksheetFunction.Match(13, Range("B:B"), 0)) + 15) .EntireRow.Hidden = 真

回答by Ammar Naseer

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.

遇到了类似的问题。原来用户已经过滤了数据，然后将其保存，并且在不关闭过滤器的情况下无法更改过滤数据集的保护。