将事件处理程序分配给在 VBA 中动态创建的用户表单上的控件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10592641/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Assign event handlers to controls on user form created dynamically in VBA
提问by BiGXERO
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 dynamicallybut 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”。对于 dayList 中的每一天，我想在用户表单上创建一个按钮，该按钮将在当天运行宏。我能够动态添加按钮，但不知道如何将 daycell.text 从命名范围传递到按钮、事件处理程序、宏：S 下面是我必须创建用户表单的代码：
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.
因此，对于我的每个 runButtons，它需要显示 daycell.text，并运行一个宏，该宏使用相同的文本作为参数来选择要执行其操作的工作表。
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 代码来处理宏的响应，但我相信必须有某种方式可以通过传递参数更优雅地完成它，只是不确定如何。提前谢谢了！
回答by SWa
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:
使用事件是因为它允许您将大部分事件用于控件。我已将按钮生成代码移动到用户表单中，如下所示：
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
这有帮助吗？为了进一步阅读，我会指向 Chip Pearson 的网站，他在大多数主题上都有很棒的东西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 的，所以不是一个成熟的 OO 语言，例如，它不支持正常意义上的继承，只支持接口继承
Hope this helps :)
希望这可以帮助 ：）
回答by andy holaday
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
