vba 如何根据先前的输入禁用单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8345418/
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
How do I disable cells based on a previous input
提问by Kaskade
I am trying to create an advanced spreadsheet in excel that caters for people who really need to be told exactly what to do and what not to do. So based on the selection in column A I want to disable some cells as follows (but only disable the cells on the same row):
我正在尝试在 excel 中创建一个高级电子表格，以迎合那些真正需要被告知该做什么和不该做什么的人。所以基于列 AI 中的选择想要禁用一些单元格如下（但只禁用同一行上的单元格）：
Number- disable cells D, F, G
Link - disable cells E, F, G
Image - disable cells D, E
Any help much appreciated, I really am not to up on my excel/vba skills.
非常感谢任何帮助，我真的不擅长我的 excel/vba 技能。
回答by Justin Self
You can lock cells so they may not be selected.
您可以锁定单元格，使其不被选中。
Range("A2").Locked = True
However, this requires that you lock the sheet (with or without a password).
但是，这要求您锁定工作表（使用或不使用密码）。
WorkSheets("Sheet1").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
WorkSheets("Sheet1").EnableSelection = xlUnlockedCells
So you would just toggle which cells are locked as the user clicks on the 'main' cell.
因此，您只需在用户单击“主”单元格时切换锁定的单元格。
In order to have this change when the user selects a new cell, you would need to hook into the Selection Change event (put the below code in your sheet module).
为了在用户选择新单元格时进行此更改，您需要挂钩选择更改事件（将以下代码放入您的工作表模块中）。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
Target is the currently selected cell. So now you could test to see which cell is currently selected and then lock the other cells as needed.
目标是当前选定的单元格。所以现在您可以测试以查看当前选择了哪个单元格，然后根据需要锁定其他单元格。
With all of that said, I would recommend against this and instead use a UserForm.
综上所述，我建议不要这样做，而是使用用户表单。
EDIT 2- Updated based on comments.
编辑 2- 根据评论更新。
Here's a simple implementation
这是一个简单的实现
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Dim tRow As Integer
tRow = Target.Row
Worksheets("Sheet1").Unprotect
Dim dLock, eLock, fLock, gLock As Boolean
Range("D" & tRow & ":G" & tRow).Locked = False
Select Case Range("b" & tRow).Value
Case "Number"
dLock = True
fLock = True
gLock = True
Case "Link"
eLock = True
fLock = True
gLock = True
Case "Image"
dLock = True
eLock = True
End Select
Range("d" & tRow).Locked = dLock
Range("e" & tRow).Locked = eLock
Range("f" & tRow).Locked = fLock
Range("g" & tRow).Locked = gLock
Worksheets("Sheet1").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Worksheets("Sheet1").EnableSelection = xlUnlockedCells
End If
End Sub
