How to create an Excel user form with dynamic combo boxes
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 WithStep 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 WithThe 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 WithStep 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).ShowPutting 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 SubNote 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
2 thoughts on “How to create an Excel user form with dynamic combo boxes”