将事件处理程序分配给VBA中动态创建的用户窗体控件

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:

我在网上找到了很多资源，它们几乎可以做我想做的事，但并不完全可以。我有一个命名范围“日表”。对于dayList中的每一天，我希望在用户表单上创建一个按钮，该按钮将运行当天的宏。我可以动态添加按钮，但不知道如何通过daycell。从命名范围，到按钮，到事件处理程序，到宏:这是我创建用户表单所需的代码:

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

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:

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

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

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.

对于每个运行按钮，它需要显示daycell。文本，并运行一个宏，该宏使用与参数相同的文本来选择工作表来执行它的工作。

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!

任何帮助都会很棒。我已经看到响应动态地编写vba代码来处理宏，但是我相信一定有某种方法可以通过传递参数来更优雅地完成它，只是不知道如何做到。提前感谢!

2 个解决方案

#1

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.

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

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

在类模块中(我称之为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:

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

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

Then we can call the useform from a separate routine:

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

Sub addLabel()
ReadingsLauncher.Show vbModeless

End Sub

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

VBA中的课程在许多VBA书籍中并没有得到很好的覆盖(通常你需要阅读VB6书籍来获得理解)，但是一旦你理解了它们以及它们是如何工作的，它们就变得非常有用:)

Hope this helps

希望这有助于

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

Would become

将成为

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

Would then be accessed:

将访问:

collBtns("Monday").Enabled = False

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

这有帮助吗?如果想要进一步阅读，我建议你去奇普皮尔森的网站，他有关于大多数话题的很棒的文章http://www.cpearson.com/excel/Events.aspx

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是基于VB6的，所以它不是一种成熟的面向对象语言，例如，它不支持常规意义上的继承，只支持接口继承

Hope this helps :)

希望这有助于:)

#2

Example of catching click on worksheet. Put this in the worksheet module:

捕获单击工作表的示例。在工作表模块中:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  ' e.g., range(A1:E1) is clicked
  If Not Application.Intersect(Target, Range("A1:E1")) Is Nothing Then
    MsgBox "You clicked " & Target.Address
  End If
End Sub

#1