How to create controls dynamically at runtime

Question

I am trying to create a variable number of controls (combo boxes) in an excel userform based on the number of columns that are on a particular worksheet being viewed. Ideally I would like to delete and existing ones and create new ones at run-time, rather than creating 100 or so and just toggling back and forth between visible and invisible. What I have at the moment will create a combo box and loop, but it only creates 1. It looks as though the combo box is being overwritten and ends with the last combo box created. Any suggestions to get all onto the same userform at run-time?

Private Sub CommandButton1_Click()
 Dim cCont As New Control

 Dim ws As Worksheet
 Dim lc As Long
 Dim i As Long

 Set ws = Loan_Data

 lc = Loan_Data.Cells(1, Columns.Count).End(xlToLeft).Column


 For i = 1 To lc
    Set cCont = Me.Controls.Add("Forms.CommandButton.1", "NewCombo" & i)
    With cCont
        .Caption = cCont.Name
        .AutoSize = True
        .Visible = True
    End With
 Next i

End Sub

Answer 1

With a little bit of math you can get that totally dynamic. Use a frame where you place the controls. Now you have coordinates relative to the frame. Use an "y_offset" and a "lineheight" variable and/or a "x_offset" and a "linewidth" variable and create controls with calculated positions (top and left). During the creation count the offset + (lines*lineheight) and set the Frames ScrollHeight (and/or ScrollWidth) according to the result...

Did that sometimes. Works very well.

Answer 2

I can share with you an example of a Procedure I used to create some ComboBoxes at Run time.

Private Sub Agrega_Combo(Unidades As Integer)
'Procedimiento para agregar los ComboBox, etiquetas y unidades a la lista.
    Dim i, j As Integer
    Dim Cmb As Control
    Dim Lbl As Control

    'Ciclo para crear los ComboBox y Etiquetas en el 'ArrUnidades'
    For i = 1 To UBound(ArrUnidades)
    'Agrega el ComboBox
        Set Cmb = Me.Controls.Add("Forms.combobox.1")
        'Se establece el nombre y la posición del nuevo ComboBox
        With Cmb
            .Name = "Combobox" & i
            .Left = 66
            .Width = 36
            If i = 1 Then
                .Top = 34
            Else
                .Top = 34 + (24 * (i - 1))
            End If
        End With
    'Agrega la Etiqueta'
        Set Lbl = Me.Controls.Add("Forms.label.1")
        With Lbl
            .Name = "Label" & i
            .Caption = ArrUnidades(i) & " :"
            .Left = 30
            .Width = 36
            If i = 1 Then
                .Top = 38
            Else
                .Top = 38 + (24 * (i - 1))
            End If
        End With
        'Ciclo para agregar las unidades indicadas al llamar el procedimiento.
        For j = 1 To Unidades
            Me.Controls("ComboBox" & i).AddItem j
        Next j
        'Selecciona el primer valor de la lista.
        Me.Controls("ComboBox" & i).Text = Me.Controls("ComboBox" & i).List(0)
    Next i
End Sub

Hope it helps.