将事件处理程序分配给在 VBA 中动态创建的用户表单上的控件 [英] Assign event handlers to controls on user form created dynamically in VBA

问题描述

我在互联网上发现许多资源可以几乎做我想做的事，但不完全是.我有一个命名范围daylist".对于 dayList 中的每一天，我想在用户表单上创建一个按钮，该按钮将在当天运行宏.我能够动态添加按钮 但不知道如何将 daycell.text 从命名范围传递到按钮、事件处理程序、宏:S 下面是我必须创建用户表单的代码:

I have found many resources on the internet that do almost what i want to do, but not quite.I have a named range "daylist". For each day in the dayList, i want to create a button on a user form that will run the macro for that day. I am able to add the buttons dynamically but dont know how to pass the daycell.text from the named range, to the button, to the event handler, to the macro :S Heres the code i have to create the user form:

Sub addLabel()
ReadingsLauncher.Show vbModeless
Dim theLabel As Object
Dim labelCounter As Long
Dim daycell As Range
Dim btn As CommandButton
Dim btnCaption As String


For Each daycell In Range("daylist")
    btnCaption = daycell.Text
    Set theLabel = ReadingsLauncher.Controls.Add("Forms.Label.1", btnCaption, True)
    With theLabel
        .Caption = btnCaption
        .Left = 10
        .Width = 50
        .Top = 20 * labelCounter
    End With

    Set btn = ReadingsLauncher.Controls.Add("Forms.CommandButton.1", "runButton", True)
    With btn
        .Caption = "Run Macro for " & btnCaption
        .Left = 80
        .Width = 80
        .Top = 20 * labelCounter
    '   .OnAction = "btnPressed"
    End With

    labelCounter = labelCounter + 1
Next daycell

End Sub

为了解决上述问题，我目前提示用户输入他们想要运行的日期(例如 Day1)并将其传递给宏并且它可以工作:

To get around the above issue i currently prompt the user to type the day they want to run (e.g. Day1) and pass this to the macro and it works:

Sub B45runJoinTransactionAndFMMS()


loadDayNumber = InputBox("Please type the day you would like to load:", Title:="Enter Day", Default:="Day1")

Call JoinTransactionAndFMMS(loadDayNumber)

End Sub

Sub JoinTransactionAndFMMS(loadDayNumber As String)
xDayNumber = loadDayNumber

Sheets(xDayNumber).Activate
-Do stuff

End Sub

因此，对于我的每个 runButton，它需要显示 daycell.text，并运行一个宏，该宏使用相同的文本作为参数来选择要执行其操作的工作表.

So for each of my runButtons, it needs to display daycell.text, and run a macro that uses that same text as a parameter to select the worksheet to do its stuff on.

任何帮助都会很棒.我见过动态编写 vba 代码来处理宏的响应，但我相信必须有某种方式可以通过传递参数更优雅地完成它，只是不确定如何.非常感谢！

Any help would be awesome. Ive seen responses that dynamically writes the vba code, to handle the macros, but i believe there must be someway it can be done a little more elegantly through passing parameters, just not sure how. Many thanks in advance!

推荐答案

我知道您现在已经接受了一个对您有用的解决方案，并且比下面的要简单得多，但是如果您有兴趣，这将是更多直接回答您的问题.

I know you have accepted a solution now that will work for you and is much simpler than the below, but if you're interested, this would be the more direct answer to your question.

您需要创建一个类来处理按钮点击，因此每次点击按钮时它都会使用类中的事件，您只需执行一次，然后为每个按钮创建一个新实例.为了阻止这些类超出范围并丢失，它们需要存储在类级别声明中.在下面，我稍微移动了您的代码.

You need to create a class to handle the button clicks, so every time the button is clicked it uses the event in the class, you only need to do this once then create a new instance of it for every button. To stop these classes going out of scope and being lost, they need storing in a class level declaration. In the below I've moved your code around a little.

在类模块中(我称之为 cButtonHandler)

In the class module (I've called it cButtonHandler)

Public WithEvents btn As MSForms.CommandButton

Private Sub btn_Click()
    MsgBox btn.Caption
End Sub

使用事件是因为它允许您将大部分事件用于控件.我已将按钮生成代码移动到用户表单中，如下所示:

With events is used as it allows you to use most of the events for the control. I've moved the button generation code into the userform as below:

Dim collBtns As Collection

Private Sub UserForm_Initialize()

Dim theLabel As Object
Dim labelCounter As Long
Dim daycell As Range
Dim btn As CommandButton
Dim btnCaption As String
'Create a variable of our events class
Dim btnH As cButtonHandler
'Create a new collection to hold the classes
Set collBtns = New Collection

For Each daycell In Range("daylist")
    btnCaption = daycell.Text
    Set theLabel = ReadingsLauncher.Controls.Add("Forms.Label.1", btnCaption, True)
    With theLabel
        .Caption = btnCaption
        .Left = 10
        .Width = 50
        .Top = 20 * labelCounter
    End With

    Set btn = ReadingsLauncher.Controls.Add("Forms.CommandButton.1", "runButton", True)
    With btn
        .Caption = "Run Macro for " & btnCaption
        .Left = 80
        .Width = 80
        .Top = 20 * labelCounter
        'Create a new instance of our events class
        Set btnH = New cButtonHandler
        'Set the button we have created as the button in the class
        Set btnH.btn = btn
        'Add the class to the collection so it is not lost
        'when this procedure finishes
        collBtns.Add btnH
    End With

    labelCounter = labelCounter + 1
Next daycell


End Sub

然后我们可以从一个单独的例程中调用useform:

Then we can call the useform from a separate routine:

Sub addLabel()
ReadingsLauncher.Show vbModeless

End Sub

VBA 中的类在许多 VBA 书籍中都没有特别好地涵盖(通常您需要阅读 VB6 书籍才能理解)，但是一旦您了解它们及其工作原理，它们就会变得非常有用:)

Classes in VBA aren't particularly well covered in many VBA books (generally you need to read VB6 books to get an understanding), however once you understand them and how they work, they become incredibly useful :)

希望能帮到你

编辑 - 解决其他查询

EDIT - to address additional queries

要引用集合中的对象，可以通过键或索引来完成.要使用密钥，您需要在将项目添加到集合时添加它，因此:

To refer to objects in a collection, this is either done through the key or the index. To use the key, you need to add it as you add the item to the collection, so:

collBtns.Add btnH

会变成

collBtns.Add btnH, btnCaption

因此，键必须是唯一的.然后可以参考如下:

For this reason, keys must be unique. You can then refer as follows:

'We refer to objects in a collection via the collection's key
'Or by it's place in the collection
'So either:
MsgBox collBtns("Monday").btn.Caption
'or:
MsgBox collBtns(1).btn.Caption
'We can then access it's properties and methods
'N.B you won't get any intellisense
collBtns("Monday").btn.Enabled = False

如果需要，您还可以向类添加其他属性/方法，例如:

You can also add additional properties/method to your class if required, so for example:

Public WithEvents btn As MSForms.CommandButton

Private Sub btn_Click()
    MsgBox btn.Caption
End Sub

Public Property Let Enabled(value As Boolean)
    btn.Enabled = value
End Property

然后会被访问:

collBtns("Monday").Enabled = False

这有帮助吗?如需进一步阅读，我会指向 Chip Pearson 的网站，他在大多数主题上都有很棒的内容 http://www.cpearson.com/excel/Events.aspx

Does this help? For further reading I would point you towards Chip Pearson's site, he has great stuff on most topics http://www.cpearson.com/excel/Events.aspx

请记住，VBA是基于VB6的，所以不是完全成熟的OO语言，例如不支持正常意义上的继承，只支持接口继承

Just remember that VBA is based on VB6 so is not a fully fledged OO language, for example, it does not support inheritance in the normal sense, only interface inheritance

希望这有帮助:)

这篇关于将事件处理程序分配给在 VBA 中动态创建的用户表单上的控件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！