CODEWITHSUNDEEP
vbaexcel
Fredrik
Fredrik

Reputation: 670

Creating and populating combobox from vba module

In Excel 2010, I am trying to create a userform dynamically in my vba code. However, after running the code, the list does not show when opening the dropdown. If I put a breakpoint before ".show", I can inspect the form in the design window and I see that the list is populated. Does the .show method clear the list or what is happening? The solutions I have found focus on populating from cell ranges or placing the ".additem"s in the initialization code of the userform. I don't want to do anything that requires me to create a second file. Everything should be in this vba code if possible. Any help appreciated. My code follows.

Sub make_combobox_form()
    ' Makes a form with only a simple combobox
    Dim myForm As Object
    Dim cb As MSForms.ComboBox

    'Create the User Form
    Set myForm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)

    With myForm
        .Properties("Width") = 400
        .Properties("Height") = 300
    End With

    Set cb = myForm.Designer.Controls.Add("Forms.ComboBox.1")
    With cb
        .Top = 100
        .Left = 100
        .Height = 20
        .Width = 200

        .AddItem "Item_1"
        .AddItem "Item_2"
        .AddItem "Item_3"
        .value = "Item_1"
    End With

    VBA.UserForms.Add(myForm.name).Show

    ThisWorkbook.VBProject.VBComponents.Remove myForm

End Sub

Upvotes: 2

Views: 1992

Answers (1)

gembird
gembird

Reputation: 14053

Sub make_combobox_form()

    'Create the User Form as component first
    Dim myFormComponent As VBComponent
    Set myFormComponent = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)

    ' Get reference to user form from the UserForms collection then
    Dim cb As MSForms.ComboBox
    Dim myForm As Variant
    Set myForm = VBA.UserForms.Add(myFormComponent.Name)

    With myForm
        .Width = 400
        .Height = 300
    End With

    Set cb = myForm.Controls.Add("Forms.ComboBox.1")
    With cb
        .Top = 100
        .Left = 100
        .Height = 20
        .Width = 200
        .AddItem "Item_1"
        .AddItem "Item_2"
        .AddItem "Item_3"
        .Value = "Item_1"
    End With

    myForm.Show

    ThisWorkbook.VBProject.VBComponents.Remove myFormComponent

End Sub

Upvotes: 3

Related Questions