将单击 VBA 函数分配给 Excel 用户窗体上动态创建的按钮
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/566770/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Assign on-click VBA function to a dynamically created button on Excel Userform
提问by notnot
I'm creating buttons dynamically on an Excel userform with the following code:
我正在使用以下代码在 Excel 用户表单上动态创建按钮：
With Me.CurrentFrame.Controls.Add("Forms.CommandButton.1")
.Caption = "XYZ"
.name = "AButton"
.Font.Bold = True
.ForeColor = &HFF&
... blah blah blah
End With
I'd like to assign a function to run when these buttons are clicked, but I can't find a straightforward way to do this since there's no property as part of the button itself.
我想指定一个函数在单击这些按钮时运行，但我找不到一种直接的方法来执行此操作，因为按钮本身没有属性。
Is there a way to do this using the above idiom? Should I be going about this whole thing in a different way?
有没有办法使用上述习语来做到这一点？我应该以不同的方式处理整件事吗？
采纳答案by DJ.
You need to dynamically create code / event handlers for each button.
您需要为每个按钮动态创建代码/事件处理程序。
It take a bit of doing - see here: http://navpadexcel.blogspot.com/2006/11/httpwwwcpearsoncomexcelvbehtm.html
这需要做一些事情 - 请参阅此处：http: //navpadexcel.blogspot.com/2006/11/httpwwwcpearsoncomexcelvbehtm.html
A better way might be to create a bunch of buttons on the form (as many as you think you'll need) ahead of time. Create the event handler code as well. Make them all hidden initially.
更好的方法可能是提前在表单上创建一堆按钮（根据您的需要而定）。还要创建事件处理程序代码。最初将它们全部隐藏。
Then when your form opens you can dynamically change the button captions, make them visible and move them around. The event code you created initially will be linked to the activated buttons as expected.
然后，当您的表单打开时，您可以动态更改按钮标题，使它们可见并四处移动。您最初创建的事件代码将按预期链接到激活的按钮。
回答by 598Bubblehead
To add a control event dynamically in an Excel form; you need to first add the event(s) in a class module. For my example, I am going to add a class module named clsTEST with one event, btn_click()
在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
As you can see, the only thing this will do is set the caption on the button to then number of times you clicked it. Next, in the form code enter the following:
如您所见，这唯一要做的就是将按钮上的标题设置为您单击它的次数。接下来，在表单代码中输入以下内容：
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
When you activate the form, it will create a button. every time you click on the button the caption will change.
当您激活表单时，它将创建一个按钮。每次单击按钮时，标题都会更改。
回答by Alex
The below code should be working
下面的代码应该工作
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"""
回答by Priyanka
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
回答by Priyanka
I've been looking at this as well. Seems you can run a macro by using the onClick property:
我也一直在看这个。似乎您可以使用 onClick 属性运行宏：
Command1.OnClick = "Macro1"
Then create a macro by that name that runs the desired function. This is my hack around this until I find something better.
然后通过该名称创建一个运行所需函数的宏。这是我解决这个问题的技巧，直到我找到更好的东西。
