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

Question

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!

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

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

Answer 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