VBA: Añadir controles desde una macro

A partir de la entrada del blog anterior, y siguiendo con la consulta de otro lector, expondré hoy una curiosidad de los formularios (UserForm) en Excel.
Vamos a añadir una serie de controles automáticamente desde la programación (NO manualmente), asociándoles además eventos a estos controles!!!.

Parece simple, pero requiere de altos conocimientos, que vamos a explicar.

La idea es añadir con programación seis TextBox que llamaremos:
'txtName1','txtName2',...,'txtName6'
a los que asociaremos uno eventos _Change.

Por otra parte añadiremos dos CommanButtton
'cmbSumaTotar','cmbCerrar'
a los que asociaremos sendos eventos _Click.

Insisto... todo esto, así como su ubicación dentro del UserForm y otras propiedades de estos controles, lo gestionaremos desde la propia programación...

El aspecto final a conseguir será:

Comenzaremos insertando un módulo de clase que renombraremos como 'ModClase1', y en esta ventana de código definiremos los eventos a emplear:

Public WithEvents txtEventos As MSForms.TextBox
Public WithEvents cmbEventos As MSForms.CommandButton
Public WithEvents cmbCerrar As MSForms.CommandButton
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub txtEventos_Change()
'gestionamos el evento Change asociado a nuestros TextBox
If Not IsNumeric(UserForm1.ActiveControl.Text) Then
    MsgBox "OJO!!, debe ser un número!!", vbCritical
    'vaciamos el textbox  y situamos el foco/cursor en ese textbox
    With UserForm1.ActiveControl
        .Text = ""
        .SetFocus
    End With
End If
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub cmbEventos_click()
'gestionamos el evento Click asociado a nuestro Botón de Suma
Dim ctrl As Control      'crea un objeto tipo Control

suma = 0
'recorre cada control dentro del formulario
For Each ctrl In UserForm1.Controls
    'comprueba si es un TextBox
    If TypeName(ctrl) = "TextBox" Then
        'y que el TextBox se llama "TxtCal..." y tiene dato
        If InStr(1, ctrl.Name, "txtName") > 0 And ctrl.Text <> "" Then
            'en ese caso acumulamos el valor del TextBox
            suma = suma + CDbl(UserForm1.Controls(ctrl.Name).Value)
        End If
    End If
Next ctrl

MsgBox "La suma es: " & suma
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub cmbCerrar_click()
'gestinamos el evento Click asociado a nuestro Botón de Salida
Unload UserForm1
End Sub

Una vez creados estos eventos y sus acciones... nos insertaremos un UserForm, y dentro de su ventana de código añadiremos el siguiente código asociado al evento _Initialize

Public txtNombres As MSForms.TextBox        'para los controles TextBox
Public cmbSuma As MSForms.CommandButton     'para el control CommandButton
Public cmbSalir As MSForms.CommandButton    'para el control CommandButton

Dim objMyEventClass() As New ModClase1       'Creamos un objeto gestionado desde un modulo de clase
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub UserForm_Initialize()
'Creamos y añadimos nuestros 3 TextBox
For i = 1 To 6
    'creamos el objeto y le damos nombre
    Set txtNombres = Me.Controls.Add("Forms.TextBox.1", "txtName" & i)
    With txtNombres     'le damos propiedades de localización
        If i <= 3 Then
            .Top = 5
            .Left = 5 + (100 * (i - 1)) + 5
        Else
            .Top = 25
            .Left = 5 + (100 * (i - 1 - 3)) + 5
        End If
        
        .Width = 100
    End With
    'añadimos un evento a cada textbox
    ReDim Preserve objMyEventClass(1 To i)
    Set objMyEventClass(i).txtEventos = txtNombres
Next i
'''''''''''''''''''''''''''''''''''''
'Creamos el botón de registro
Set cmbSuma = Me.Controls.Add("Forms.CommandButton.1", "cmbSumaTotal")
With cmbSuma
    .Top = 50
    .Left = 5
    .Width = 50
    .Height = 25
    .Caption = "Sumar"
End With
'añadimos un evento al botón
Set objMyEventClass(1).cmbEventos = cmbSuma
'''''''''''''''''''''''''''''''''''''
Set cmbSalir = Me.Controls.Add("Forms.CommandButton.1", "cmbCerrar")
With cmbSalir
    .Top = 50
    .Left = 55
    .Width = 50
    .Height = 25
    .Caption = "Salida"
End With
'añadimos un evento al botón
Set objMyEventClass(1).cmbCerrar = cmbSalir
End Sub

Listo. Con esto conseguimos lo deseado... generar automáticamente los controles de nuestro formulario, asignádoles los eventos necesarios para su correcta gestión. Un aspecto clave en este código es la definición como Array de la clase creada: Dim objMyEventClass() As New ModClase1 que luego se utiliza para personalizar y asignar a cada TextBox el evento Change correspondiente. Otro punto relevante es el método .Add que se emplea para añadir los distintos controles... que tiene tres partes: SetControl = object. Add(tipo control, Nombre, Visible) Un trabajo delicado pero muy interesante sin duda...