How to dynamically create events in VBA?

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

1 - Define the name of the controls in the call of Me.Controls.Add (this is another parameter after the progID of the class). 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 the name (although it does not really need it).

2 - Access the VBA project to insert lines of code manually.

To do item 2, just look at this my other answer here in SOPT. The example there inserts a comment, and you will have to enter everything the code of your function (s).

  

It is worth noting that this approach has serious problems / difficulties.   First, you will have to write the code of the function that will be   call in the click event inside another code, making it less   readable and harder to maintain. Secondly, this changes the   VBA project, so you'll need to save the Excel file to   each execution. Third, it will be difficult to remove labels   because you'll have to search the code for the exact name of the function,   find the exact% of it to remove, etc. And finally (and   mainly), this requires the enablement of a configuration that   generates a potential security problem (read more in response   referenced).

A better alternative (whose original source is this SOEN response ) is to build a class (a class module, to be more accurate) and make it treat all click events for all labels. Here's how:

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

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 property / attribute of the class named LabelHandler (again, call as you see fit - just remember to change the handle function appropriately: Ctrl ) and implement < in> generic code to handle the click on the object defined in that property.

3 - In your original code, move the labels array out from the scope of your role. It needs to be out of scope because it must continue to exist once the function is finished. Also, make this array save instances of the new class it created ( <nome da propriedade>_Click() , in my example) rather than directly from the Label created.

4 - Finally, add the created label 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 "Test" title button was only used to invoke LabelHandler ):

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

  

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