excel - 如何将事件添加到 Excel VBA 用户窗体中动态创建的控件(按钮、列表框)

Question

我正在尝试实时构建用户表单。 (在 Excel VBA 中)只是为了尝试，我从 2 个标签、一个文本框、一个组合框和一个命令按钮开始。我在下面列出了代码 + 结果表格。

我的问题是:如何获得链接到控件的事件，特别是组合框和命令按钮？通常(对于手动创建的表单)，这将通过称为 cmbTabel_change() 和 cmdExit_click() 的例程来完成。但是，当它们是动态创建时，这似乎不起作用。谁能帮帮我？

代码:

Private Sub UserForm_Initialize()
    Dim cCont As Control

    Call Add_Control(cCont, "Label", "lblDatabase", "Database", 30, 23, 60, 18)
    Call Add_Control(cCont, "Textbox", "txtDatabase", "Database", 110, 20, 60, 18)
    Call Add_Control(cCont, "Label", "lblTabel", "Tabel", 30, 47, 90, 18)
    Call Add_Control(cCont, "Combobox", "cmbTabel", "Tabel", 110, 44, 90, 18)
    Call Add_Control(cCont, "CommandButton", "cmdExit", "Afsluiten", 210, 140, 54, 18)
End Sub

Private Sub Add_Control(ctrl, ctp, cnm, cap, l, t, w, h)
    Set ctrl = Me.controls.Add("Forms." & ctp & ".1", cnm)
    With ctrl
        .Left = l
        .Top = t
        .Width = w
        .Height = h
    End With

    Select Case ctp
    Case "Combobox"
        controls(cnm).Clear
        For j = 1 To 5
            controls(cnm).AddItem "ListItem" & j
        Next j
        controls(cnm).ListIndex = 0
    Case "Label", "CommandButton"
        With controls(cnm)
            .Caption = cap
        End With
    Case "Textbox"
        controls(cnm).Text = cap
    End Select
End Sub

结果形式:

Answer 1

您需要构建一个类来处理控件上的事件。

例如，创建一个名为 clsMyEvents 的新类模块。
将此代码添加到类中:

Option Explicit

Public WithEvents MyCombo As MSForms.ComboBox
Public WithEvents MyButton As MSForms.CommandButton

Private Sub MyCombo_Change()
    MsgBox MyCombo.Name & " was changed to value " & MyCombo.Value
End Sub

Private Sub MyButton_Click()

    Dim BtnNum As Long

    BtnNum = Replace(MyButton.Name, "MyButton", "")

    MsgBox MyButton.Name & " is " & IIf(BtnNum Mod 2 = 0, "even", "odd")

End Sub

注意:当您输入 WithEvents 时，您将能够选择与该类型的控件关联的大多数 事件。

接下来，创建一个空白的 UserForm 并添加以下代码:

Option Explicit

Public MyEvents As New Collection

Private Sub UserForm_Initialize()

    Dim tmpCtrl As Control
    Dim CmbEvent As clsMyEvents
    Dim x As Long

    'Add some dummy data for the combo-boxes.
    Sheet1.Range("A1:A5") = Application.Transpose(Array("Red", "Yellow", "Green", "Blue", "Pink"))
    Sheet1.Range("B1:B5") = Application.Transpose(Array(1, 2, 3, 4, 5))
    Sheet1.Range("C1:C5") = Application.Transpose(Array(5, 4, 3, 2, 1))

    For x = 1 To 5
        'Add the control.
        Set tmpCtrl = Me.Controls.Add("Forms.ComboBox.1", "MyCombobox" & x)
        With tmpCtrl
            .Left = 10
            .Width = 80
            .Top = (x * 20) - 18 'You might have to adjust this spacing.  I just made it up.
            .RowSource = "Sheet1!" & Sheet1.Cells(1, x).Resize(5).Address
        End With

        'Attach the event.
        Set CmbEvent = New clsMyEvents
        Set CmbEvent.MyCombo = tmpCtrl
        MyEvents.Add CmbEvent

    Next x

    For x = 1 To 5
        Set tmpCtrl = Me.Controls.Add("Forms.CommandButton.1", "MyButton" & x)
        With tmpCtrl
            .Left = 100
            .Width = 50
            .Height = 20
            .Top = (x * 20) - 18
            .Caption = "Num " & x
        End With

        Set CmbEvent = New clsMyEvents
        Set CmbEvent.MyButton = tmpCtrl
        MyEvents.Add CmbEvent
    Next x

End Sub

编辑:我已经更新以包含命令按钮的代码。当您更改组合框中的值时，它会告诉您控件的名称以及更改后的值。当您点击一个按钮时，它会告诉您按钮上的数字是奇数还是偶数。