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

问题描述

我在互联网上发现了许多资源，这些资源几乎就是我想要做的，但不太合适。我有一个命名范围daylist。对于dayList中的每一天，我想在用户表单上创建一个将在该日期运行宏的按钮。我可以动态添加按钮a>但不知道如何将daycell.text从命名范围传递到按钮，到事件处理程序到宏：S我必须创建用户形式的代码：

  Sub addLabel（）
 ReadingsLauncher.Show vbModeless 
 Dim theLabel As Object 
 Dim labelCounter As Long 
 Dim daycell作为范围
 Dim btn As CommandButton 
 Dim btnCaption As String 
 
 
对于每个daycell In Range（daylist）
 btnCaption = daycell.Text 
设置theLabel = ReadingsLauncher.Controls.Add（Forms.Label.1，btnCaption，True）
 WithLabel 
 .Caption = btnCaption 
 .Left = 10 
 .Width = 50 
 .Top = 20 * labelCounter 
结束
 
设置btn = ReadingsLauncher.Controls.Add（Forms.CommandButton.1，runButton，True ）
用btn 
 .Caption =运行宏为& btnCaption 
 .Left = 80 
 .Width = 80 
 .Top = 20 * labelCounter 
'.OnAction =btnPressed
结束
 
 labelCounter = labelCounter + 1 
 Next daycell 
 
 End Sub 
  

为了解决上述问题，我目前提示用户键入要运行的日期（例如Day1），并将其传递给宏，它的工作原理是：

  Sub B45runJoinTransactionAndFMMS（）
 
 
 loadDayNumber = InputBox（请输入您要加载的日期，Title：=Enter Day默认值：=Day1）
 
调用JoinTransactionAndFMMS（loadDayNumber）
 
 End Sub 
 
 Sub JoinTransactionAndFMMS（loadDayNumber As String）
 xDayNumber = loadDayNumber 
 
表格（xDayNumber）。激活
  - 东西
 
 End Sub 
  

所以对于每个我的runButtons，它需要显示daycell.text，并运行一个使用相同文本作为参数的宏选择工作表来做它的东西。

任何帮助都会很棒。我看到响应动态写vba代码，以处理宏，但我相信必须有一些它可以做一点更优雅的传递参数，只是不知道如何。非常感谢您提前！

解决方案

我知道您已经接受了一个可以为您工作的解决方案，并且比以下简单，但是如果你有兴趣的话，这将是对你的问题的直接的回答。

你需要创建一个类来处理按钮的点击，所以每次按钮被点击，它使用类中的事件，你只需要这样做一次，然后为每个按钮创建一个新的实例。为了阻止这些课程超出范围并被丢失，他们需要存储在一个类级别的声明中。在下面我已经移动了你的代码。

在类模块中（我已经称它为cButtonHandler）

  Public WithEvents btn As MSForms.CommandButton 
 
 Private Sub btn_Click（）
 MsgBox btn.Caption 
 End Sub 
  

使用事件，因为它允许您将大多数事件用于控件。我将按钮生成代码移动到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 
'创建一个我们的事件变量class 
 Dim btnH As cButtonHandler 
'创建一个新的集合来保存类
设置collBtns =新集合
 
对于每个daycell In Range（daylist）
 btnCaption = daycell.Text 
设置theLabel = ReadingsLauncher.Controls.Add（Forms.Label.1，btnCaption，True）
 WithLabel 
 .Caption = btnCaption 
 .Left = 10 
 .Width = 50 
 .Top = 20 * labelCounter 
结束
 
设置btn = ReadingsLauncher.Controls.Add（Forms.CommandButton.1，runButton，True）
带有btn 
 .Caption =运行宏为& btnCaption 
 .Left = 80 
 .Width = 80 
 .Top = 20 * labelCounter 
'创建一个新的事件类实例class 
 Set btnH = New cButtonHandler 
'将我们创建的按钮设置为类
中的按钮设置btnH.btn = btn 
'将该类添加到集合中，以使其不会丢失
'，当此过程完成
 collBtns.Add btnH 
结束
 
 labelCounter = labelCounter + 1 
下一个daycell 
 
 
 End Sub 
  

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

  Sub addLabel（）
 ReadingsLauncher.Show vbModeless 
 
 End Sub 
   
 
 
希望这有帮助
 
 
 
编辑 - 解决其他查询
 
 
 
对于集合中的对象，可以通过键或索引来完成。要使用该密钥，您需要将其添加到集合中，因此：
  collBtns.Add btnH 
  
将成为
  collBtns.Add btnH，btnCaption 
  
因此，密钥必须是唯一的。您可以参考以下内容：
 '我们通过集合的密钥
'或通过它的位置在集合
'所以任一：
 MsgBox collBtns（星期一）。btn.Caption 
'或：
 MsgBox collBtns（1）.btn.Caption 
'然后我们可以访问它的属性和方法
'NB你不会得到任何intellisense 
 collBtns（星期一）btn.Enabled = False 
  
如果需要，您还可以向课程添加其他属性/方法，例如：
  Public WithEvents btn As MSForms.CommandButton 
 
 Private Sub btn_Click（）
 MsgBox btn.Caption 
 End Sub 
 
公共属性Let Enabled（value As Boolean）
 btn.Enabled = value 
结束属性
  
然后可以访问：
  collBtns（Monday）。Enabled = False 
  
这是否有帮助？为了进一步阅读，我会指向Chip Pearson的网站，他对大多数主题都有很好的作用。 http：// www.cpearson.com/excel/Events.aspx  
 
 
 
只要记住，VBA是基于VB6的，所以不是一个完整的OO语言，例如，它不支持正常意义上的继承，只有接口继承
 
 
 
希望这有助于：）
 
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
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
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.

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.

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
Then we can call the useform from a separate routine:
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 :) 

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

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

Hope this helps :)

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