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

发布于 2024-07-13 17:35:32 字数 358 浏览 8 评论 0原文

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

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

我想指定一个函数在单击这些按钮时运行，但我找不到直接的方法来执行此操作，因为没有属性作为按钮本身。

有没有办法使用上面的习惯用法来做到这一点？我应该以不同的方式来处理这整件事吗？

原文

I'm creating buttons dynamically on an Excel userform with the following code:

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

酒废 2024-07-20 17:35:33

在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

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

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()

    '#### 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.

回复收藏 0 原文

仙女山的月亮 2024-07-20 17:35:33

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

这需要做一些工作 - 请参阅此处：http://navpadexcel.blogspot.com/2006/11 /httpwwwcpearsoncomexcelvbehtm.html

更好的方法可能是提前在表单上创建一堆按钮（您认为需要的数量）。还要创建事件处理程序代码。最初将它们全部隐藏。

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

回复收藏 0 原文

救星 2024-07-20 17:35:33

下面的代码应该可以工作

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"""

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"""

回复收藏 0 原文

安静 2024-07-20 17:35:33

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

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

回复收藏 0 原文