How to create an Excel user form with dynamic combo boxes

by Archana Oswal in Combo box

In this tutorial we will see how to create a user form using VBA and dynamic combo boxes to it.

The input sheet contains a number of drop downs with varying number of options in each, as seen below:

Using VBA, we will create two dynamic drop-downs, first to select the category (name, color and so on) and the second to select the corresponding options (column B onwards). The options in the second drop-down will be populated automatically based on the selection of the first drop-down.

Let us look at the code step-wise

Contents

Step 1: Creating the form

A new user form is created using the Add method in VBComponents (the vbext_ct_MSForm constant is for type MS Form. It’s caption,  width and height properties are then set using the code below:

    Set newForm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)

    'Set it's properties
    With newForm
        .Properties("Caption") = "Dynamic Combo Boxes"
        .Properties("Width") = 200
        .Properties("Height") = 150
    End With




Step 2: Add the first Combo Box


Next, we need to add the category combo box and set it’s properties


    'Add the first Combo Box for categories
    Set categoryComboBox = newForm.designer.Controls.Add("Forms.combobox.1")

    With categoryComboBox
        .Name = "cbCategory"
        .Top = 20
        .Left = 20
        .Width = 150
        .Height = 20
    End With




The name of the combo box will be used further in the code


Step 3: Add the next combo box


Similar to the first combo box, we will add the second combo box for options and set it’s properties


    'Add the second Combo Box for options
    Set optionsComboBox = newForm.designer.Controls.Add("Forms.combobox.1")
    With optionsComboBox
        .Name = "cbOptions"
        .Top = 60
        .Left = 20
        .Width = 150
        .Height = 20
    End With




Step 4: Add code to populate the category combo box


As the number of categories is dynamic, we will have to dynamically populate the category combo box each time the form is loaded.


Code module of a form is referred to using “newForm.CodeModule”. The InsertLines method is used to add a line of code at a particular line number.


Note that we have started inserting the code at line 2. This is because, if “Require Variable Declaration” is enabled, first line of the code will always be “Option Explicit” and we want our code to be placed after that.


“UserForm_Initialize” is the sub that will be called when a user form is loaded. So, we will insert our code in this sub. The AddItem method is used to populate the combo box.


    'Add code to the newly created form
    'Initialize the first combo box in the UserForm_Initialize subroutine
    'Values will be taken from the first column in the spreadsheet
    newForm.CodeModule.InsertLines 2, "Private Sub UserForm_Initialize()"
    newForm.CodeModule.InsertLines 3, " Dim lastRow as Integer"
    newForm.CodeModule.InsertLines 4, " Dim i as Integer"
    newForm.CodeModule.InsertLines 5, " lastRow = Sheet1.Cells(Sheet1.Rows.Count, ""A"").End(xlUp).Row"
    newForm.CodeModule.InsertLines 6, " For i = 1 To lastRow"
    newForm.CodeModule.InsertLines 7, "     cbCategory.AddItem Sheet1.Cells(i, 1)"
    newForm.CodeModule.InsertLines 8, " Next"
    newForm.CodeModule.InsertLines 9, "End Sub"



Step 5: Add code to populate the options combo box


Each time the category combo box selection is changed, the corresponding options for that selection need to be populated in the second combo box. Let us see the code for that.


We will use the “cbCategory_Change” sub for populating the options combo box (where cbCategory is the name of the category combo box). This sub will be fired whenever there is a change in the first combo box selection.


First, we will need to clear the existing options that have been added to the second combo box. Then starting from column B, till the last column is reached, we will continue adding the options to the combo box.


    'When ever there is change in the first combo box selection, cbCategory_Change will be fired
    'Populate the second combo box with the options corresponding to the selected category
    newForm.CodeModule.InsertLines 10, "Private Sub cbCategory_Change()"
    'Clear previously added options
    newForm.CodeModule.InsertLines 11, "    cbOptions.Clear"
    newForm.CodeModule.InsertLines 12, "    Dim i as Integer"
    'Start from column B
    newForm.CodeModule.InsertLines 13, "    For i = 2 to 10"
    newForm.CodeModule.InsertLines 14, "        If Sheet1.Cells(cbCategory.ListIndex+1, i)="""" Then"
    newForm.CodeModule.InsertLines 15, "            Exit For"
    newForm.CodeModule.InsertLines 16, "        End If"
    newForm.CodeModule.InsertLines 17, "    cbOptions.Additem Sheet1.Cells(cbCategory.ListIndex+1, i)"
    newForm.CodeModule.InsertLines 18, "    Next"
    newForm.CodeModule.InsertLines 19, "End Sub"



Step 6: Display the form


    'Add and display the newly added form
    VBA.UserForms.Add(newForm.Name).Show



Putting the entire code together:


Sub createUserFormWithDynamicComboBoxes()
    Dim newForm As Object
    Dim newFrame As MSForms.Frame
    Dim categoryComboBox As MSForms.ComboBox
    Dim optionsComboBox As MSForms.ComboBox

    Application.VBE.MainWindow.Visible = False

    'Create a new user form
    'vbext_ct_MSForm is the constant for an MS Form
    Set newForm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)

    'Set it's properties
    With newForm
        .Properties("Caption") = "Dynamic Combo Boxes"
        .Properties("Width") = 200
        .Properties("Height") = 150
    End With

    'Add the first Combo Box for categories
    Set categoryComboBox = newForm.designer.Controls.Add("Forms.combobox.1")

    With categoryComboBox
        .Name = "cbCategory"
        .Top = 20
        .Left = 20
        .Width = 150
        .Height = 20
    End With

    'Add the second Combo Box for options
    Set optionsComboBox = newForm.designer.Controls.Add("Forms.combobox.1")
    With optionsComboBox
        .Name = "cbOptions"
        .Top = 60
        .Left = 20
        .Width = 150
        .Height = 20
    End With

    'Add code to the newly created form
    'Initialize the first combo box in the UserForm_Initialize subroutine
    'Values will be taken from the first column in the spreadsheet
    newForm.CodeModule.InsertLines 2, "Private Sub UserForm_Initialize()"
    newForm.CodeModule.InsertLines 3, " Dim lastRow as Integer"
    newForm.CodeModule.InsertLines 4, " Dim i as Integer"
    newForm.CodeModule.InsertLines 5, " lastRow = Sheet1.Cells(Sheet1.Rows.Count, ""A"").End(xlUp).Row"
    newForm.CodeModule.InsertLines 6, " For i = 1 To lastRow"
    newForm.CodeModule.InsertLines 7, "     cbCategory.AddItem Sheet1.Cells(i, 1)"
    newForm.CodeModule.InsertLines 8, " Next"
    newForm.CodeModule.InsertLines 9, "End Sub"

    'When ever there is change in the first combo box selection, cbCategory_Change will be fired
    'Populate the second combo box with the options corresponding to the selected category
    newForm.CodeModule.InsertLines 10, "Private Sub cbCategory_Change()"
    'Clear previously added options
    newForm.CodeModule.InsertLines 11, "    cbOptions.Clear"
    newForm.CodeModule.InsertLines 12, "    Dim i as Integer"
    'Start from column B
    newForm.CodeModule.InsertLines 13, "    For i = 2 to 10"
    newForm.CodeModule.InsertLines 14, "        If Sheet1.Cells(cbCategory.ListIndex+1, i)="""" Then"
    newForm.CodeModule.InsertLines 15, "            Exit For"
    newForm.CodeModule.InsertLines 16, "        End If"
    newForm.CodeModule.InsertLines 17, "    cbOptions.Additem Sheet1.Cells(cbCategory.ListIndex+1, i)"
    newForm.CodeModule.InsertLines 18, "    Next"
    newForm.CodeModule.InsertLines 19, "End Sub"

    'Add and display the newly added form
    VBA.UserForms.Add(newForm.Name).Show
End Sub



Note that, each time you run the code, a new form will be created, which will look like




Whenever you change the selection in the first combo box, you will see the second combo box populated accordingly, as seen below




Note that, you need to include the below libraries under Tools > Reference for the code to compile




For further reading, refer to the below articles


 VBA UserForm Drop Down Lists


Excel Drop Down Lists


Excel VBA Drop Down Lists Using Data Validation

2 thoughts on “How to create an Excel user form with dynamic combo boxes”

Leave a Reply

Your email address will not be published. Required fields are marked *