将一个VBA函数分配给在Excel用户窗体上动态创建的按钮

Question

我正在使用以下代码在Excel用户窗体上动态创建按钮：

With Me.CurrentFrame.Controls.Add("Forms.CommandButton.1")
    .Caption = "XYZ"
    .name = "AButton"
    .Font.Bold = True
    .ForeColor = &HFF&
    ... blah blah blah
End With

我想在这些按钮被点击时分派一个函数来运行，但由于没有作为按钮本身的属性，因此我无法找到一个简单的方法来实现这一点。

是否有一种方法可以使用上面的习惯用语来做到这一点？我应该以不同的方式完成整个事情吗？

- notnot

Answer 1

要在Excel表单中动态添加控件事件，您需要先在类模块中添加事件。以我的示例为例，我将添加一个名为clsTEST的类模块，并带有一个事件，btn_click()。

    '#### CLASS NAMED clsTEST
    Public WithEvents btn As MSForms.CommandButton
    Public frm As UserForm

    Dim iCount As Long

    Private Sub btn_Click()

    iCount = IIf(iCount < 1, 1, iCount + 1)
    btn.Caption = "Count " & Str(iCount)

End Sub
'### END CLASS

正如您所看到的，这样做的唯一效果是将按钮上的标题设置为您单击它的次数。 接下来，在表单代码中输入以下内容：

    Dim mColButtons As New Collection    '## SET A NEW COLLECTION

    Private Sub UserForm_Activate()
    '
    Dim btnEvent As clsTEST
    Dim ctl As MSForms.Control
    '
    Set ctl = Me.Controls.Add("Forms.CommandButton.1")
    '
    With ctl
    .Caption = "XYZ"
    .Name = "AButton"
    END With
    '
    Set btnEvent = new clsTEST   
    Set btnEvent.btn = ctl
    set btnEvent.frm = Me
    '
    mColButtons.add btnEvent
    'End Sub

当您激活表单时，它将创建一个按钮。每次单击按钮时，标题都会更改。

- 598Bubblehead

---

这对我非常有效。我想知道是否有一种方法可以将变量发送到类中。例如，如果我要更改.caption为我发送的任何内容。Private Sub btn_Click(var1 As String)我尝试过这个，但结果是“对象不支持此属性或方法”。 - JahKnows

没错，_Click() 事件不接受任何参数。相反你可以尝试在类中设置另一个私有变量，首先给它赋值，然后将事件设置到处理程序。 - PravyNandas

Answer 2

你需要为每个按钮动态创建代码/事件处理程序。

这需要一些操作-请看这里：http://navpadexcel.blogspot.com/2006/11/httpwwwcpearsoncomexcelvbehtm.html

更好的方法可能是预先在表单上创建一堆按钮（数量根据需要确定），同时创建事件处理程序代码。最初将它们全部隐藏。

然后当表单打开时，您可以动态更改按钮标题、使其可见并移动它们。最初创建的事件代码将与激活的按钮链接，就像预期的那样。

- DJ

---

聪明的想法！似乎在 VBA 中需要聪明才行。 - notnot

Answer 3

以下代码应该可以正常工作。

Dim NewButton As OLEObject
Dim CodeModule As Object

Set NewButton = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
    Link:=False, DisplayAsIcon:=False, Left:=52.5, Top:=Hght, _
    Width:=202.5, Height:=26.25)
NewButton.Object.Caption = "Click Me!"
Set CodeModule = ActiveWorkbook.VBProject.VBComponents.VBE.ActiveCodePane.CodeModule
CodeModule.InsertLines CodeModule.CreateEventProc("Click", NewButton.Name) + 1, vbTab & "MsgBox ""Hello world"""

- Alex

Answer 4

Sub Oval1_Click()
    file = ActiveWorkbook.Name
    Set Output = Workbooks.Add()
    ActiveWorkbook.SaveAs Filename:="Try.xls"        
    Sheets(1).Select        
    ActiveSheet.Buttons.Add(460, 10, 140, 30).Select
    ActiveSheet.Buttons.Text = "DATA"      
    ActiveSheet.Shapes("Button 1").Select
    Selection.OnAction = "Book1.xlsm!data_Click"
End Sub

Sub data_Click()      
    MsgBox "you have clicked me"       
    ActiveSheet.DrawingObjects.Delete        
End Sub

- Priyanka

Answer 5

我也一直在研究这个问题。看起来可以通过使用onClick属性来运行宏：

Command1.OnClick = "Macro1"

然后创建一个同名的宏来运行所需的函数。 这是我的临时解决方案，直到我找到更好的方法。

- Matt Carr

---

对于Excel用户窗体控件，分配“onClick”导致“对象不支持此属性或方法”。 - PravyNandas