2022 updates MS Excel - Data Cleaning Course Enroll now  
Excel VBA CodeTutorials

Create a Dependent Combobox in Excel and VBA

3 Mins read

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

List box & a Combobox is 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:

Excel VBA Macros for Non-Coders

Do you find VBA Macros scary? Did you miss any job opportunity because of it? Get started today with our eBook guide on using – Excel VBA Macros. 140 pages of rich visuals. Download now.

List 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

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

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’ names and giving the name “Animals”.

Fruits Data Picture-5

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

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’ names.

Data Validation Box Picture-7

After setting the same as 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

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

Dependent List Blank Picture-9

Excel VBA Macros for Non-Coders

Do you find VBA Macros scary? Did you miss any job opportunity because of it? Get started today with our eBook guide on using – Excel VBA Macros. 140 pages of rich visuals. Download now.

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 combo box is connected with the list for each category.
Here also, we will do the same 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

Sub loadparts(a)
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

1. The module code should look like below

Coding Picture-10

2. The UserForm code should look like below

Useform Coding 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

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

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

Excel VBA Macros for Non-Coders

Do you find VBA Macros scary? Did you miss any job opportunity because of it? Get started today with our eBook guide on using – Excel VBA Macros. 140 pages of rich visuals. Download now.

Related posts
AlteryxTutorials

Alteryx Hotkeys - Alteryx Keyboard Shortcuts

2 Mins read
Top 50+ Alteryx Shortcuts for Windows. Alteryx is popularly known as a Self-Service Analytics tool. Business users can build their data workflows…
AlteryxTutorials

Step By Step Guide to Learn Alteryx

6 Mins read
Alteryx Learning Path: The growth in technology has resulted in growth in understanding. In today’s world, humans – fellow businessmen know the…
Excel VBA CodeTutorials

VBA Code to Clean the Date Format

1 Mins read
When it is useful? Most of the time the most annoying problem is when the data is taken from ERP or other…