将单击 VBA 函数分配给 Excel 用户窗体上动态创建的按钮 [英] Assign on-click VBA function to a dynamically created button on Excel Userform

问题描述

我正在使用以下代码在 Excel 用户表单上动态创建按钮:

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?

推荐答案

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

You need to dynamically create code / event handlers for each button.

需要做一些事情 - 请参阅此处:http://navpadexcel.blogspot.com/2006/11/httpwwwcpearsoncomexcelvbehtm.html

It take a bit of doing - see here: 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.

这篇关于将单击 VBA 函数分配给 Excel 用户窗体上动态创建的按钮的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！