Create ComboBox's and AddItems to them all within the VBA code

Question

I need to create ComboBox's and then AddItems to each ComboBox. This will all be done to a userform. I need to do this entirely within the VBA code, this is because each time the userform is opened new information will be shown.

this is what I have so far:

    Private Sub UserForm_Initialize()

    for i = 1 to size
    Set CmbBX = Me.Controls.Add("Forms.ComboBox.1")
        CmbBX.Top = ((90 * i) - 18) + 12 + 20
        CmbBX.Left = 30
        CmbBX.Text = "Please select an item from the drop down"
        CmbBX.TextAlign = fmTextAlignCenter
        CmbBX.Width = 324
        CmbBX.Visible = False
        CmbBX.Name = "ComBox2" & i

    Next
    end sub

the problem is, once each ComboBox is created its like its name isnt there. I cannot referance the combobox. this is what I have tried:

ComBox21.AddItems "Test1"
ComBox22.AddItems "Test2"

And it errors out. When I look at the UserForms function bar at the top of the screen (where I would usually select ComBox22_Change() for example), It shows that no ComboBoxes even exist!

Any Ideas on how to dynamically create and additems to comboboxes?

Thank you in advance

Possible duplicate of [Excel VBA Add Items to Dynamically Created ComboBox](https://stackoverflow.com/questions/39042960/excel-vba-add-items-to-dynamically-created-combobox) — aaa, Mar 13 '18 at 23:52

score 0 · Accepted Answer · answered Mar 14 '18 at 00:11

Here an sample of the code.

You need still to change it for you needs but this will be easy.

I have created a simple userform and one button to do test and it works fast.

To imput the comboboxes replace ".additem" with a loop to load each of them.

How to do that -- search in google

how to Populate a combobox with vba

You cannot refferance any controls on userform if they dont exist.

You need to search for them after creation and then modify them.

Example below with button code.

I think this should bring you to an idea how to manage this.

Option Explicit

Private Sub CommandButton1_Click()
Dim refControl As Control, frm As UserForm
Dim x
Set frm = Me
With Me
For Each x In Me.Controls
If TypeName(x) = "ComboBox" Then
    Select Case x.Name
        Case "cmbDemo3"
        MsgBox "works!"
        'here you can put your code
    End Select
    MsgBox x.Name
End If
Next x
End With
End Sub

Private Sub UserForm_Initialize()
Dim combobox_Control As Control
     Dim i

    For i = 0 To 5
         Set combobox_Control = Controls.Add("forms.combobox.1")
         With combobox_Control
             .Name = "cmbDemo" & i
             .Height = 20
             .Width = 50
             .Left = 10
             .Top = 10 * i * 2
             .AddItem "hihi" 'here you can add your input code
         End With
     Next i
End Sub

With this code, How can I use the cmbDemo3_Change() event? My goal is cascading combo box's. — Austin, Mar 14 '18 at 14:50
I have found the solution to what I asked above Here: https://stackoverflow.com/questions/3014421/how-to-add-events-to-controls-created-at-runtime-in-excel-with-vba — Austin, Mar 14 '18 at 15:53

Create ComboBox's and AddItems to them all within the VBA code

1 Answers1