Assign event handlers to controls on user form created dynamically in VBA

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 🙂

More Related Contents:

Leave a Comment Cancel reply