vba 从下拉列表中选择时触发事件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17339568/
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
Trigger event when select from dropdown
提问by Ivan Lee
I need when a user selects an option from the dropdown menu, it will trigger the event and lock down certain range of cells. I got the codes for lockdown cell but I can't lock it down when I select the dropdown menu. The value of the string data in the dropdown menu is ZFB50
我需要当用户从下拉菜单中选择一个选项时，它将触发事件并锁定特定范围的单元格。我得到了锁定单元格的代码，但是当我选择下拉菜单时无法锁定它。下拉菜单中字符串数据的值为ZFB50
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$K" Then
With Application
.EnableEvents = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
If Target.Address = "ZFB50" Then
ActiveSheet.Unprotect
Range("E8:E100").Select
Selection.Locked = True
Range("C8:C100").Select
Selection.Locked = True
Range("D8:D100").Select
Selection.Locked = True
Range("F8:F100").Select
Selection.Locked = True
Next cell
ActiveSheet.Protect
With Application
.EnableEvents = True
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End If
End Sub
It still doesn't work, is there any problem with this code?
还是不行，请问这段代码有问题吗？
回答by sous2817
If you're using a data validation dropdown, you can use the Worksheet_Change event like so:
如果您使用数据验证下拉菜单，您可以像这样使用 Worksheet_Change 事件：
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A" Then
With Application
.EnableEvents = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
' Code to lock ranges goes here
With Application
.EnableEvents = True
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End If
End Sub
This assumes that your data validation is in cell A1. You'll have to update the reference as appropriate for your situation.
这假设您的数据验证在单元格 A1 中。您必须根据您的情况更新参考。
回答by Sebastian Viereck
an example can be found here with named ranges:
可以在此处找到带有命名范围的示例：
http://www.sebastianviereck.de/en/VBA-dropdown-change-event-to-perform-action/
http://www.sebastianviereck.de/en/VBA-dropdown-change-event-to-perform-action/
