vba 锁定范围内的某些单元格

Lock certain cells in a range

提问by Ryan E

I'm trying to loop through a range of cells, locking any cell that has content while leaving empty cells unlocked.

我正在尝试遍历一系列单元格，锁定任何有内容的单元格，同时不锁定空单元格。

When I run the below code the result is the entire sheet is locked. If I add an else statement the sheet is unlocked. Basically whatever the last .locked = (true, false) statement is is how the entire sheet winds up.

当我运行下面的代码时，结果是整个工作表被锁定。如果我添加一个 else 语句，工作表将被解锁。基本上无论最后一个 .locked = (true, false) 语句是如何整个工作表结束。

Change 1Is it possible that I have some setting on/off that is interfering since I'm the only one who is unable to get any of this to work?

更改 1是否有可能我有一些干扰的开/关设置，因为我是唯一一个无法使这些设置正常工作的人？

Sub ProtectTheSheet()
Dim chCell As Range
Dim chRng As Range

'Clear the default status
ActiveSheet.Unprotect
Range("A7:I35").Locked = False

Set chRng = ActiveSheet.Range("A7:I35")

'Check cell value in body and lock cells with content
For Each chCell In chRng.Cells
    If chCell.Value <> "" Then Cells.Locked = True
Next chCell

ActiveSheet.Protect

End Sub

回答by Tim Williams

Sub ProtectTheSheet()
    Dim chCell As Range
    Dim chRng As Range

    ActiveSheet.Unprotect
    Set chRng = ActiveSheet.Range("A7:I35")

    'Check cell value in body and lock cells with content
    For Each chCell In chRng.Cells
        chCell.Locked = (chCell.Value <> "")
    Next chCell

    ActiveSheet.Protect

End Sub

回答by Sidharth Panwar

Check this out: http://www.mrexcel.com/archive/VBA/15950b.html

看看这个：http: //www.mrexcel.com/archive/VBA/15950b.html

Sub CellLocker()
Cells.Select
' unlock all the cells
Selection.Locked = false
' next, select the cells (or range) that you want to make read only, 
' here I used simply A1
Range("A1").Select
' lock those cells
Selection.Locked = true
' now we need to protect the sheet to restrict access to the cells. 
' I protected only the contents you can add whatever you want
ActiveSheet.Protect DrawingObjects:=false, Contents:=true, Scenarios:=false
End Sub

If you say Range("A1").Select, then it locks only A1. You can specify multiple cells to be locked by specifying as follows:
A3:A12,D3:E12,J1:R13,W18
This locks A3 to A12 and D3 to E12 etc.

如果你说 Range("A1").Select，那么它只锁定 A1。您可以通过如下指定来指定要锁定的多个单元格：
A3:A12,D3:E12,J1:R13,W18
这将锁定 A3 到 A12 和 D3 到 E12 等。

回答by Ragala Santosh Kumar

You can try this.

你可以试试这个。

Public Sub abc()
ActiveSheet.Unprotect Password:="1234"
ActiveSheet.Range("I8:I500, K8:K500, M8:M500, N8:N500").Cells.Locked = False
ActiveSheet.Protect Password:="1234"
End Sub

回答by Arjun Handa

If you want to protect the specific cells of any specific excel without the password protection then here is the solution:

如果您想在没有密码保护的情况下保护任何特定 excel 的特定单元格，那么这里是解决方案：

Sub ProtectingSheet()

  Workbooks.Open (c\documents\....)

  Dim mainworkBook As Workbook

  Set mainworkBook = ActiveWorkbook

  Worksheets(CellValue).Activate

  mainworkBook.Sheets("Sheet1").Range("A1:AA100").Locked = True

  Range(Cells(1, 2), Cells(1, 25)).Select
  Selection.Locked = False

  ActiveSheet.Protect

End Sub

回答by Ed Bolton

I may be missing something but...

我可能会遗漏一些东西，但是......

Cells.Locked = True

...will lock all cells on the active sheet. If you just change it to...

...将锁定活动工作表上的所有单元格。如果只是将其更改为...

chCell.Locked = True

...then it works; I think?! As the range is very small, you may as well not unlock cells at the start, and instead unlock cells whilst locking them e.g.

...然后它起作用了；我认为？！由于范围非常小，您最好不要在开始时解锁单元格，而是在锁定单元格的同时解锁单元格，例如

For Each chCell In chRng.Cells
    If chCell.Value <> "" Then 
    chCell.Locked = True
    Else
    chCell.Locked = False
    End If
Next chCell

If you are new to VBA, I would recommend cycling through code line-by-line as described in this Excel consultant's video. If you step through code, you can check "has cell A7 behaved as expected?"...instead of just seeing the end product

如果您不熟悉 VBA，我建议您按照Excel 顾问的视频中所述逐行循环代码。如果您单步执行代码，您可以检查“单元格 A7 是否按预期运行？”...而不仅仅是查看最终产品

回答by brettdj

A quick way to unlock non-blank cells is to use SpecialCells see below.

解锁非空白单元格的快速方法是使用 SpecialCells，见下文。

On my testing this code handles merged cells ok, I think this is what is generating your error on Tim's code when it looks to handle each cell individually (which to be clear is not an issue in Tim's code, it is dealing with an unexpected outcome)

在我的测试中，这段代码可以处理合并的单元格，我认为这就是在 Tim 的代码看起来单独处理每个单元格时会产生错误的原因（要明确的是，这在 Tim 的代码中不是问题，它正在处理意外的结果)

You may also find this article of mine A fast method for determining the unlocked cell rangeuseful

您可能还会发现我的这篇文章确定解锁单元格范围的快速方法很有用

Sub Quicktest()
    Dim rng1 As Range
    Dim rng2 As Range
    On Error Resume Next
    Set rng1 = ActiveSheet.Range("A7:I35").Cells.SpecialCells(xlFormulas)
    Set rng2 = ActiveSheet.Range("A7:I35").Cells.SpecialCells(xlConstants)
    On Error GoTo 0
    ActiveSheet.Unprotect
    ActiveSheet.Range("A7:I35").Cells.Locked = False
    If Not rng1 Is Nothing Then rng1.Cells.Locked = True
    If Not rng2 Is Nothing Then rng2.Cells.Locked = True
    ActiveSheet.Protect
End Sub

回答by logicOnAbstractions

I know this is an old thread, but I've been stuck on this for a while too, and after some testing on Excel 2013 here's what I conclude if your range includes any merged cell

我知道这是一个旧线程，但我也已经坚持了一段时间，在对 Excel 2013 进行了一些测试后，如果您的范围包含任何合并的单元格，我得出的结论是

• The merged cells must be entirely included within that range (e.g. the merging must be entirely within the range being lock/unlocked
• The range being merged can be larger, or at least exactly the range corresponding to the merged cells. If it's a named range that works as well.

• 合并的单元格必须完全包含在该范围内（例如合并必须完全在被锁定/解锁的范围内
• 被合并的范围可以更大，或者至少是与合并单元格对应的范围。如果它是一个也有效的命名范围。

Also, you cannot lock/unlock a cell that is already within a protected range. E.g if you run:

此外，您无法锁定/解锁已经在受保护范围内的单元格。例如，如果您运行：

public sub test()
   Sheet1.range("myNameRange").locked = true
   Sheet1.protect
end sub

Twice it will work the first time, and fail the second time around. So you should unprotect the target range (or the sheet) before....

第一次会成功两次，第二次会失败。因此，您应该在...之前取消保护目标范围（或工作表）。