将一个点击的VBA功能分配给Excel Userform上的一个动态创建的按钮 [英] Assign on-click VBA function to a dynamically created button on Excel Userform

问题描述

我正在使用Excel用户形式动态创建按钮，具有以下代码：

  With Me.CurrentFrame.Controls.Add （Forms.CommandButton.1）
 .Caption =XYZ
 .name =AButton
 .Font.Bold = True 
 .ForeColor =& HFF& 
 ... blah blah blah 
结束
  

我想分配一个功能来运行这些按钮被点击，但我找不到一个直接的方式来做到这一点，因为没有属性作为按钮本身的一部分。

是有没有办法使用上述成语？我应该用不同的方法去完成这件事吗？

解决方案

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

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

更好的方法可能是创建一堆表单上的按钮（尽可能多的你认为需要）提前。创建事件处理程序代码。让它们全部隐藏起来。

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

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.

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 Userform上的一个动态创建的按钮的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！