vba 动态锁定 Excel 中的单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9686789/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dynamically locking cells in Excel
提问by venkat ch
I have four columns (A,B,C,D). My requirement is that in each row, only one cell should accept data, and the remaining cells should not accept data (i.e. the remaining three cells should be in locked mode).
我有四列（A、B、C、D）。我的要求是在每一行中，只有一个单元格应该接受数据，其余的单元格不应接受数据（即其余三个单元格应处于锁定模式）。
How do I do this?
我该怎么做呢？
回答by Jean-Fran?ois Corbett
Here's one way to do what you ask. (Or at least what I think you're asking.)
这是按照您的要求进行操作的一种方法。（或者至少我认为你在问什么。）
First, unlock all the cells in the sheet. By default they are all in locked state (though this does not take effect until you actually protect the sheet) so you need to unlock them to start with, otherwise you won't be able to input any data anywhere as soon as you protect the sheet. You can do this manually (In 2003: Select all cells, Format > Cells > Protection > uncheck "Locked") or using VBA as follows:
首先，解锁工作表中的所有单元格。默认情况下，它们都处于锁定状态（尽管这在您实际保护工作表之前不会生效），因此您需要先解锁它们，否则一旦保护了工作表，您将无法在任何地方输入任何数据床单。您可以手动执行此操作（在 2003 中：选择所有单元格，格式 > 单元格 > 保护 > 取消选中“锁定”）或使用 VBA，如下所示：
Sub UnlockAll()
With Sheet1 ' Or whatever sheet you're working on
.Unprotect
.Cells.Locked = False
.Protect
End With
End Sub
The above only needs to be executed once.
以上只需要执行一次。
Then you have to use the Worksheet_Changeevent to lock and unlock cells dynamically. In your sheet's module, write this code:
然后您必须使用该Worksheet_Change事件动态锁定和解锁单元格。在您的工作表模块中，编写以下代码：
Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect
If Not IsEmpty(Target) Then
'Data was added in target cell. Lock its neighbours.
Me.Cells(Target.Row, 1).Resize(, 4).Locked = True
Target.Locked = False
Else
'Data was erased from target cell. Release its neighbours.
Me.Cells(Target.Row, 1).Resize(, 4).Locked = False
End If
Me.Protect
End Sub
Say you write data in cell B2; this will lock cells B1, B3, and B4 so that no data can be entered. If you later decide to clear cell B2, then this will unlock B1, B3, and B4.
假设您在单元格 B2 中写入数据；这将锁定单元格 B1、B3 和 B4，以便无法输入任何数据。如果您稍后决定清除单元格 B2，则这将解锁 B1、B3 和 B4。
