从数据验证下拉菜单中触发Excel VBA中的事件 [英] Trigger an event in Excel VBA from a data validation drop down

问题描述

我有一张工作表，我想让用户选择计算类型.通过数据验证中的列表选择来完成计算类型.一旦选择，我希望它触发一个事件，然后将为该选择类型加载正确的单元格.如何在数据验证"下拉列表中检测到数据更改事件，或者我需要为此使用主动x控件?

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?

工作表更改事件的代码未激活:

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

推荐答案

您的代码很好.我在新的工作簿中尝试过，它确实可以完成它的工作.
当您将 D47 中的值更改为"+"(通过下拉菜单或手动)时，它会在单元格 D33 中一个接一个地写入六个值.

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 D47 to "+" (whether by dropdown or manually) it writes six values one after another in a cell D33.

也许你想写

        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"

因此，代码将填充范围 D33:I33 ，而不是将所有内容都写入 D33 .

so the code will fill range D33:I33 rather than writing everything into D33.

这篇关于从数据验证下拉菜单中触发Excel VBA中的事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！