用一个事件处理程序处理多个UserForm控件-VBA Excel [英] Handling Multiple UserForm Controls With One Event Handler - VBA Excel

问题描述

我动态创建了许多按钮(创建计划)，并希望它们在Click事件(onClick属性)期间执行相同的操作。

当然，我可以预先在表单上创建最大数量的按钮，并将它们设置为不可见，等等，同时在它们的Click事件上添加"Call SomeEvent"，考虑到可能有一千多个按钮。这将是非常繁琐的。

因此，简化：

我创建了新类btnClass`

Public WithEvents ButtonEvent As MsForms.CommandButton
Private Sub ButtonEvent_Click()
    MsgBox "hey"
End Sub

然后，在我的动态创建按钮的UserForm中，我以其简化形式添加了以下内容(我还有Collection，用于稍后删除按钮)：

Dim btnColl As Collection
Dim Buttons As New btnClass

Set btnColl = New Collection
Set Buttons = New btnClass

For i = 0 To btnCount

         Set theButton = Controls.Add("Forms.CommandButton.1", "btn" & i, True)
         With theButton
            .Height = 17
            .Caption = "btn" & i
          End With


        Set Buttons.ButtonEvent = theButton
        btnColl.Add theButton, theButton.Name


Next i

但当我单击动态创建的按钮时没有任何反应。我错过了什么？

-更新 -@FaneDuru提供了适合我的解决方案

 ReDim Buttons(0 To btnCount, 0 To dtDiff)


For labelcounter = 0 To dtDiff 'add date labels
    Set theLabel = Controls.Add("Forms.Label.1", "lblDay" & labelcounter, True)
    With theLabel

        .Caption = VBA.Format(DateAdd("d", labelcounter, bDate), "d-mm-yy")
        .Left = 15 + 44 * labelcounter
        .BackColor = vbBlack
        .Font.Bold = True
        .ForeColor = vbWhite
        .Height = 13
        .Width = 40
        .Top = 85
    End With
    For i = 0 To btnCount 'add time buttons
        pTime = DateAdd("n", i * dur, begTime)
        Set theButton = Controls.Add("Forms.CommandButton.1", "btn" & CDate(theLabel.Caption & " " & TimeValue(pTime)), True)
        With theButton
            .Height = 17
            .Caption = VBA.Format(TimeValue(pTime), "hh:mm")
            '.Caption = CDate(theLabel.Caption & " " & TimeValue(pTime))
            .Left = 15 + 44 * labelcounter
            .BackColor = vbGreen
            .Width = 40
            .Top = 100 + 18 * i
        End With


    Set Buttons(i, labelcounter).ButtonEvent = theButton
    btnColl.Add theButton, theButton.Name



    Next i

Next labelcounter

推荐答案

这样，只为最后创建的按钮分配事件。您必须声明一个类数组...我还使用了一些新创建的按钮的Left属性，只是为了测试它们的Click事件。请尝试下一种方法：

Option Explicit

Private btnColl As New Collection
Dim Buttons() As New btnClass

Private Sub btCreate_Click()
 Dim btnCount As Long, theButton As CommandButton, i As Long

 btnCount = 3
 ReDim Buttons(0 To btnCount)
 For i = 0 To btnCount

         Set theButton = Me.Controls.aDD("Forms.CommandButton.1", "btn" & i, True)
         With theButton
            .height = 17
            .Caption = "btn" & i
            .left = 50 * i
          End With

        btnColl.aDD theButton, theButton.Name
        Set Buttons(i).ButtonEvent = theButton
 Next i
End Sub

Private Sub btdelete_Click() 'buttons deletion...
 Dim i As Long
   For i = 1 To btnColl.count
       Me.Controls.Remove (btnColl(i).Name)
   Next
End Sub

这篇关于用一个事件处理程序处理多个UserForm控件-VBA Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！