Create a Dependent Combobox in Excel and VBA

In this blog, we will discuss how to create a Combobox in Excel and as well as through VBA.

List box & a Combobox are similar but have slightly different controls, to let users make multiple choices of items or to enter their own value in a list. Typical examples of items in these types of lists are employee names, exchange rates, and product items.

We all know how to create a validation list in Excel. But very few of us know how to create a dependent list in Excel. For Example, you have the validation list looks like below:

List Picture-1

(Picture 1)

Now my point is that if I choose “Animals” from the drop down menu, then a list of animal names will be shown in the drop down.

Animals List Picture-2

(Picture 2)

Related article: Export data from SQL to Excel spreadsheet using VBA

Similarly, from the same box (Picture 1) if I choose “Fruits” the same box will be shown the list of fruit names. To do the same you need to set the name range. I am thinking that your data looks like below.

Data Picture-3

(Picture 3)

Now I will create the named range for all of these items are shown in Picture 1. We will give the same name for each and every category. I am choosing all the animals’ name and giving the name as “Animals”.

Fruits Data Picture-5

Animals Data Picture-4

 

 

 

 

 

 

 

 

 

(Picture 4) & (Picture 5)

Similarly, for the Fruits, Vegetables and for the cell phone I have aerated the name manager and there are 4 names on my list. Remember, we need to give the same name what is there for the first validation you have chosen.
Name Manager Picture-6

(Picture 6)

Now we will choose the cell where the dependent list will be created. In my case, my validation list in H2 cell and I am going to dependent validation list on I2. After choosing I2 cell I go Data and then Data Validation from the Data Tools section. Then Choose List from Allow section and in the Source, I have written the formula =indirect($H$2). It is the trick. The value of H2 is Animals and we have already set and Animals mean a list of animals’ name.

Data Validation Box Picture-7

(Picture 7)

After setting the same as like above please click on OK and your dependent is ready to use. You can choose any particulars from the first validation box and the dependent list will be populated.
Vegetable List Picture-8

(Picture 8)

Related article: 5 Powerful Formatting Tricks in Excel

So your dependency list ready. Only a small formula (indirect) has done the trick. For Indirect formula in details, you can go through our training materials.

Dependent List Blank Picture-9

(Picture 9)

Now to do the same thing in VBA is a little bit tricky. I am creating 1 combobox and 1 list box in the user form and the combobox is connected with the list for each category.
Here also, we will do the same the thing. If you choose 1 category from the first box (combobox) then the dependent value in the same list box will be populated. To do the same thing, please follow the below steps for creating the above UserForm:

  1. Insert a new module
  2. Paste the below code
  3. Open UserForm
  4. Double click on the Form
  5. Paste the below UserForm code

Module Code:

Sheets("sheet1").Select

Dim lists()

b = 2

x:

If Cells(b, a) <> "" Then

ReDim Preserve lists(1 To b - 1)

lists(b - 1) = Cells(b, a)

b = b + 1: GoTo x

End If

UserForm1.ListBox1.List = lists()

End Sub

The below codes we will use in UserForm.

Private Sub ComboBox1_Change()

Sheets("sheet1").Select

Dim x(1): x(1) = ""

a = ComboBox1.ListIndex

If a = -1 Then GoTo endd

loadparts (a + 2)

 

endd:

End Sub

 

Private Sub UserForm_Initialize()

Sheets("sheet1").Select

Dim List(): Dim x(1)

r = Range("A" & Rows.Count).End(xlUp).Row

For a = 1 To r

ReDim Preserve List(1 To a)

List(a) = Cells(a, 1).Text

Next a

x(1) = ""

UserForm1.ComboBox1.List = List()

UserForm1.ListBox1.List = x()

End Sub

Sub loadparts(a)

The module code should look like below:

Coding Picture-10

(Picture 10)

The UserForm code should look like below:

Useform Coding Picture-11

(Picture 11)

Please run the code or press F5 to run the code. You will get the UserForm and choose one category from the Combobox and as soon as we will choose any value, then the dependent value in the List box will be populated.

Dependent List Fruits Picture-12

(Picture 12)

I have selected a Fruits category from the box and the dependent value has now populated.

Hope you have found this as useful: Please download the excel macro code file and play with it.

Now if I tell you to select the 3 fruits from the Listbox and save them in a place how will do you the same? I will cover that part on my next blog. Till then, enroll in our excel dashboard course lessons

Related Tutorials

Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
OFFSET Function in Excel
July 6, 2018
Strikethrough Shortcut in Excel & Word
July 4, 2018
INDIRECT Function with SUM, MAX, MIN & Independent Cell Value
June 29, 2018
Pivot Table Slicers In Excel
June 12, 2018
How to Split Cells in Excel using Text to Column
June 7, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
How to Hide/Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
Learn how to remove blank cells in Excel
June 3, 2018
How to Group Numbers, Dates & Text in Pivot table in Excel
June 1, 2018
5 Powerful Tricks to Format cells in Excel
May 31, 2018
Insert a Picture into a Cell in Excel
May 25, 2018
What is ISFORMULA Function and FORMULATEXT Function
May 21, 2018
How to Use SUBSTITUTE Function
May 21, 2018
Excel Quartile Function in Excel
May 8, 2018
How to use the Excel PERCENTILE function
May 7, 2018
Insert or Type degree symbol in Excel with Autocorrect Feature
May 7, 2018
25% Discount
No prize
All Courses at $200
Almost!
10% Discount
Free Ebook
No Prize
No luck today
Almost!
50% Discount
No prize
80% Discount
Get a chance to boost your knowledge!
Use this coupon code for any course that you wish for. 
Our in-house rules:
  • You can choose any course & redeem coupon
  • If you find any difficulty, mail us on [email protected]
  • Wheel Spin will end soon
  • Coupon code can be applied within 2 days.