vba - 检测在 Excel 运行时添加的组合框上的事件

Question

我在 Excel 中的 VBA 脚本有问题。我所做的基本上是创建按钮，按下时会在其中一个工作表中创建一组两个额外的组合框。可以连续按下此按钮以添加更多组合框。

这些新创建的组合框的行为如下:

创建了 2 个组合框

Combobox1 将在控制表

中加载一些列表

Whenever an item in Combobox1 is selected, Combobox2 will load list of items to be added to Combobox2

添加按钮的代码是这样的

 Sub Add_Criteria()

       Dim controlNum As Integer
       Dim name1 As String
       Dim name2 As String
       Dim oOle1 As OLEObject
       Dim oOle2 As OLEObject
       Dim uniqueString As String

       Dim cb1 As Object

        controlNum = Sheets("Controls").Range("A16").Value

        'adding Control
        Set oOle1 = Sheets("System").OLEObjects _
            .Add(ClassType:="Forms.ComboBox.1", Left:=10, _
            Top:=75 + (controlNum * 20), Width:=100, Height:=18)
        Set oOle2 = Sheets("System").OLEObjects _
            .Add(ClassType:="Forms.ComboBox.1", Left:=120, _
            Top:=75 + (controlNum * 20), Width:=100, Height:=18)

        'adding properties
        oOle1.Name = "Criteria" & controlNum * 2 - 1
        oOle2.Name = "Criteria" & controlNum * 2

        'adding control var
        Sheets("Controls").Range("A16").Value = controlNum + 1

        With oOle1.Object
           .List = Sheets("Controls").Range("A5:A13").Value
        End With

  End Sub

问题是，我无法检测到它上面的事件。我需要更改组合框 1 中的值更改时创建的第二个组合框上显示的值。我尝试使用以下引用，但我仍然不能。谁能指导我如何做到这一点

引用(几天来一直在这个问题上):

http://www.dbforums.com/microsoft-excel/1641165-detecting-click-event-dynamically-created-controls.html (这是用户表单，我不知道为什么我不能在表格中复制它)

Answer 1

您可以以编程方式添加事件。下面的代码为每个组合框添加了一个事件

此引用来自 Pearson Programming The VBA Editor也可能有用。

Sub Add_Criteria()

    Dim controlNum As Integer
    Dim name1 As String
    Dim name2 As String
    Dim oOle1 As OLEObject
    Dim oOle2 As OLEObject
    Dim uniqueString As String
    Dim cb1 As Object
    Dim strCode As String
    Dim vbProj As Object
    Dim vbCodeMod As Object

    Set vbProj = ActiveWorkbook.VBProject
    Set vbCodeMod = vbProj.vbcomponents(ActiveSheet.CodeName).codemodule

    controlNum = Sheets("Controls").Range("A16").Value

    'adding Control
    Set oOle1 = Sheets("System").OLEObjects.Add(ClassType:="Forms.ComboBox.1", Left:=10, Top:=75 + (controlNum * 20), Width:=100, Height:=18)
    Set oOle2 = Sheets("System").OLEObjects.Add(ClassType:="Forms.ComboBox.1", Left:=120, Top:=75 + (controlNum * 20), Width:=100, Height:=18)


    vbCodeMod.AddFromString AddEvent(oOle1.Name)
    vbCodeMod.AddFromString AddEvent(oOle2.Name)


    'adding properties
    oOle1.Name = "Criteria" & controlNum * 2 - 1
    oOle2.Name = "Criteria" & controlNum * 2

    'adding control var
    Sheets("Controls").Range("A16").Value = controlNum + 1

    With oOle1.Object
        .List = Sheets("Controls").Range("A5:A13").Value
    End With

End Sub

Function AddEvent(strIn As String) As String
    AddEvent = "Private Sub " & strIn & "_Click()" & Chr(10) & _
              "MsgBox ""Event Added""" & Chr(10) & _
              "End Sub"
End Function