How to create events dynamically in VBA?

Question

The code below creates Labels dynamically and now need to control the mouse action ("Click" event) on each Label.

For example, pass the text source ("Caption") to bold if you are regular (unclouded).

There is How to create the event "Click" and control it individually for each Label in this case?

Private Sub CriaLabels(ByVal QuantidadeDeLabels As Integer)

    Dim i As Integer

    Dim NewLabel(QuantidadeDeLabels-1) As Object

    For i = 0 To QuantidadeDeLabels-1

        Set NewLabel(i) = Me.Controls.Add("Forms.Label.1")

        With NewLabel(i)
            .Caption = "NewLabel" & i
            .Top = 50 * i
            .Left = 50
        End With

    Next i

End Sub

Answer 1

There are a few alternatives. You can try to dynamically create a new click handling function for each label, using the format <nome do controle>_Click(), but for that you will need:

1 - Set the name of the controls in the call Me.Controls.Add (is another parameter after the class progID). As the code will need to reference the object by name, it is not enough to just define the "caption". The code below makes this definition of name (although it doesn’t really need).

2 - Access the VBA project to enter the code lines manually.

To make item 2, just look this my other answer here in SOPT. The example there inserts a comment, and you will have to insert whole the code(s) of its function(s) (s).

It is worth noting that this approach has serious (as) problems/difficulties. First, you will have to write the code of the function that will be call in the click event within another code, making it less readable and more difficult to maintain. Second, this changes the VBA project, so that you will need to save the Excel file to each run. Third, it will become difficult to remove Abels because you will have to search the code by the exact name of the function, find the end sub exact of it to remove, etc. And finally (and mainly), this requires enabling a configuration that generates potential security problem (read more in response referenced).

A better alternative (whose original source is this response from the SOEN) is to build a class (a class module, to be more exact) and make it treat all click events for all Abels. Do so:

1 - Create a class module with any name you want (in my example, I called LabelHandler).

2 - In it, put the following code:

Public WithEvents Ctrl As MSForms.Label

Private Sub Ctrl_Click()

    MsgBox "Você clicou no label de nome " & Ctrl.Name

End Sub

This code simply declares a class property/attribute called Ctrl (again, call as you please - just remember to change the click handling function appropriately: <nome da propriedade>_Click()) and implements the generic code to process the click on the object defined in that property.

3 - In your original code, move the array of controls (Abels) out of the scope of its function. It needs to be outside the scope because it needs to continue existing after the function ends. Also, make this matrix save instances of the new class you created (LabelHandler, in my example) rather than directly from the created label.

4 - Finally, add the control (label) created to the property of the new treatment class. Here is the code:

Dim Labels() As New LabelHandler

Private Sub CriaLabels(ByVal QuantidadeDeLabels As Integer)

    Dim i As Integer
    Dim Label As Control

    ReDim Labels(0 To QuantidadeDeLabels - 1)

    For i = 0 To QuantidadeDeLabels - 1

        Set Label = Me.Controls.Add("Forms.Label.1", "NewLabel" & i)

        With Label
          .Caption = "NewLabel" & i
          .Top = 50 * i
          .Left = 50
        End With

        Set Labels(i).Ctrl = Label

    Next i

End Sub

This code generates the following result (for a test creating 4 Labels - the title button "Test" was only used to invoke CriaLabels 4):

Note that the generic code executed in the class LabelHandler can handle differences based on the clicked object by checking its name, for example.

Final Note: In the future, look for code. Facilitates your own reading and maintenance, and also who will try to help you around here.