从数据验证下拉列表中触发 Excel VBA 中的事件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14619676/
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 an event in Excel VBA from a data validation drop down
提问by Marc L
I have a sheet where I want to give the user a choice of calculation types. The calculation types are done via a list selection in Data validation. Once selected, I want it to trigger an event which will then load the correct cells for that type of selection. How do I detect a data change event on the Data validation drop down or do I need to use the active x control for this?
我有一个工作表，我想在其中为用户提供计算类型的选择。计算类型通过数据验证中的列表选择完成。选择后，我希望它触发一个事件，然后为该类型的选择加载正确的单元格。如何在数据验证下拉菜单中检测数据更改事件，或者我是否需要为此使用活动 x 控件？
Code for the worksheet change event not activating:
未激活工作表更改事件的代码：
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.count > 1 Then Exit Sub
Application.EnableEvents = False
On Error GoTo Errortrap
'~~> Change it to the relevant string with which you want to compare
StringToCheck = "+"
If Not Intersect(Target, Range("D47")) Is Nothing Then
'~~> Check for the cell value
If Target.Value = StringToCheck Then
'setup row to capture addition fields
Cells(33, 4).Value = "Input File 1"
Cells(33, 4).Value = "Worksheet 1"
Cells(33, 4).Value = "Cell 1"
Cells(33, 4).Value = "Input File 2"
Cells(33, 4).Value = "Worksheet 2"
Cells(33, 4).Value = "Cell 2"
End If
End If
LetsContinue:
Application.EnableEvents = True
Exit Sub
Errortrap:
MsgBox Err.Description
Resume LetsContinue
End Sub
回答by Nordik
Your code is fine.
I tried it in a new workbook and it does just what it supposed to do.
When you change the value in D47to "+" (whether by dropdown or manually) it writes six values one after another in a cell D33.
你的代码没问题。我在一本新的工作簿中尝试了它，它完成了它应该做的事情。
当您将D47 中的值更改为“+”（无论是通过下拉列表还是手动）时，它会在单元格D33 中一个接一个地写入六个值。
Maybe you meant to write
也许你想写
Cells(33, 4).Value = "Input File 1"
Cells(33, 5).Value = "Worksheet 1"
Cells(33, 6).Value = "Cell 1"
Cells(33, 7).Value = "Input File 2"
Cells(33, 8).Value = "Worksheet 2"
Cells(33, 9).Value = "Cell 2"
so the code will fill range D33:I33rather than writing everything into D33.
所以代码将填充范围D33:I33而不是将所有内容写入D33。
