adding controls to form automatically

[baitmaster]

Greetings chaps and chapesses... long time no speak, for I have usually managed to use the MrExcel SEARCH facility to find what I need...

On this occasion however, I would once again like to trouble someone for a bit of slightly more complex theory, regarding VBA UserForms

I have a 3-column lookup table in a worksheet, containing a list of options. This table is dynamic, i.e. more or less options could be in it at any time. I would like to generate a userform based on the contents of this table, as follows:

If field 1 contains data, I would like a label and a combobox to be added to my form. The label will be the contents of field 1. The combobox will default to field 2, the list of options available being from another generic list, the same for every combobox. Changing the combobox will change field 2

I am interested in knowing how to add the 2 objects, and define how to call a macro when the combo is changed etc. Currently, I have to hard-code the form, which makes it tricky to just add new rows to the lookup table

Below are examples of the different sections of code I am using, so you can see how repetitive they are. I am hoping that these will be reduced by e.g. looping my form controls

I think the key is understanding how to refer to a control, using a variable name, so I can use a [FOR i = 1 TO 10] type approach, and refer to "comboBox" & i as an object - something like that

Thanks for looking

Initialise form:

Private Sub UserForm_Initialize()
 
refreshFunctionArray [COLOR=seagreen]' defines the arrScenarioList array, based on my 3 col lookup table[/COLOR]
 
[COLOR=seagreen]' set values in comboboxes. loadScenarios is a function, returning the value I need[/COLOR]
ComboBox1.Clear: ComboBox1 = loadScenarios(1): Label1.Caption = arrScenarioList(1, 1)
ComboBox2.Clear: ComboBox2 = loadScenarios(2): Label2.Caption = arrScenarioList(2, 1)
ComboBox3.Clear: ComboBox3 = loadScenarios(3): Label3.Caption = arrScenarioList(3, 1)
 
[COLOR=seagreen]' add all available options to comboboxes[/COLOR]
For Each cl In Range("scenariosList")
    If cl.Value <> "" Then
        ComboBox1.AddItem cl.Value
        ComboBox2.AddItem cl.Value
        ComboBox3.AddItem cl.Value
    End If
Next cl
 
End Sub

Changing comboboxes:

Private Sub ComboBox1_Change()
If ComboBox1.Value <> "" Then updateScenarios 1, ComboBox1.Value
End Sub
 
Private Sub ComboBox2_Change()
If ComboBox2.Value <> "" Then updateScenarios 2, ComboBox2.Value
End Sub
 
Private Sub ComboBox3_Change()
If ComboBox3.Value <> "" Then updateScenarios 3, ComboBox3.Value
End Sub

Resetting all boxes at once:

Private Sub CommandButton2_Click()
Application.Calculation = False
 
Dim ctl As MSForms.Control
Dim combo As MSForms.ComboBox
 
For Each ctl In frmControls.Controls
    If TypeOf ctl Is MSForms.ComboBox Then ctl.Value = "baseline"
Next ctl
 
Application.Calculation = True
End Sub

This last bit is some new code to me, so it might also be the basis for a good way to do this. Any pointers welcome!