在Excel中，我在运行时在用户窗体上添加按钮。如何添加这些按钮操作来调用带参数的子/函数？
下面是我当前添加按钮的方式，它不接受我添加它的"OnAction

Dim MyR As Range, MyB As MSForms.Control

For i = 2 To ActiveSheet.UsedRange.Rows.Count
    If (Len(ActiveSheet.Cells(i, 1).Value) > 0) Then
        
        Set MyR = Range(ActiveSheet.Cells(i, 1), ActiveSheet.Cells(i, 1))
        Set MyB = UserForm1.Controls.Add("Forms.CommandButton.1")
        
        With MyB
            .Name = i
            .Left = 50
            .Top = 25 * i
    
            .Caption = "Caption text"
            
            .OnAction = "SomeFunction"
            .Parameter = i
        End With
        
    End If
Next i

上面的代码在. OnAction上失败，我找不到如何通过编程方式在此处添加一个操作，每个按钮的参数都不相同。

• • 更新**

下面的部分工作，但只有最后一个事件已设置的工作，所以只有最后一个按钮工作的预期

Dim i As Integer
    Dim MyB As MSForms.Control
    Dim btnEvent As MyCustomButton
    
    For i = 2 To ActiveSheet.UsedRange.Rows.Count
        If (Len(ActiveSheet.Cells(i, 1).Value) > 0) Then
            
            Set MyB = UserForm1.Controls.Add("Forms.CommandButton.1")
            
            With MyB
                .Name = ActiveSheet.Cells(i, 1).Value
                .Caption = ActiveSheet.Cells(i, 1).Value
                .Left = 10
                .Top = 25 * i
                .Width = 75
                .Height = 20
                .Tag = ActiveSheet.Cells(i, 1).Value
            End With
            
            Set btnEvent = New MyCustomButton
            Set btnEvent.btn = MyB
            Set btnEvent.frm = UserForm1
            
            If (i > 4) Then 'TODO delete this loop
                Exit For
            End If
            
        End If
    Next i
    
    UserForm1.Show

• • 更新2**

好吧，如果你在一个循环中创建多个事件，似乎需要将事件存储在某个地方才能使它们工作。将它们存储在集合中为我解决了这个问题。在以下位置找到：Excel-VBA Capture all worksheet CommandButton click events to run universal code
感谢@Maciej Los的帮助
如果有人觉得这有用，下面是我的工作模块：

Option Explicit

Sub tests()
    
    Dim i As Integer
    Dim MyB As MSForms.Control
    Dim btnEvent As MyCustomButton
    Dim colButtons As Collection
    
    Set colButtons = New Collection
    
    For i = 2 To ActiveSheet.UsedRange.Rows.Count
        If (Len(ActiveSheet.Cells(i, 1).Value) > 0) Then
            
            Set MyB = UserForm1.Controls.Add("Forms.CommandButton.1")
            
            With MyB
                .Name = ActiveSheet.Cells(i, 1).Value
                .Caption = ActiveSheet.Cells(i, 1).Value
                .Left = 10
                .Top = 25 * i
                .Width = 75
                .Height = 20
                .Tag = ActiveSheet.Cells(i, 1).Value
            End With
            
            Set btnEvent = New MyCustomButton
            Set btnEvent.btn = MyB
            Set btnEvent.frm = UserForm1
            colButtons.Add btnEvent
            
        End If
    Next i
    
    UserForm1.Show
    
End Sub

以及必要的类模块：

Public WithEvents btn As MSForms.CommandButton
Public frm As UserForm

Private Sub btn_Click()
    Debug.Print (btn.Tag)
End Sub

还需要一个用户表单，此处命名为"UserForm1"。