August 6, 2020

How to Create Dependent Combobox in VBA Excel?

We know how to create a cascading dropdown on a worksheet using data validation. But how do we create a cascading combobox dropdown on VBA userform. This is one of the basic needs.

In this article, we will learn how to create a dependent combobox in VBA.

Logic For Dependent Combobox in VBA

To create a dependent combobox in VBA we need to initialize the combobox on the event of the source element. For example, if a combobox depends on a radio button group, then the code to load dependent combobox should run immediately after someone selects a radio button. Or if a combobox depends on another combobox then depending on the combobox should load every time the value in the combobox is changed.

Enough of the theory. Let’s jump into an example to see how it works.

ComboBox Depending on Another Combobox

Create two combobox. First one should list the few names of countries. The other should list the names of states of the selected country. If user changes the country in in first combobox, the list of second combobox should be changed.

Let’s create a form that has two combobox with labels countries and states and a command button to submit the input.

The first combobox should list the names of countries and it does not depend on any value. So we will load it in form_intialize event as we do for basic combobox initialization.

Double click on the userform. It will open the coding area in UserForm Object. Now from the left dropdown menu select userform. Then from the right dropdown menu select initialize.

An empty sub name UserForm_Initialize() will be inserted. Anything written in this sub will be executed before the userform shows up.

So we write the initialization code for combobox here.

Private Sub UserForm_Initialize()


 countries = Array("India", "Nepal", "Bhutan", "Shree Lanka")

 UserForm1.ComboBox1.List = states


End Sub

We have our first combobox initialized. Whenever you will load the user form, the first combox will be ready with the name of countries.

Now to load the second combobox, we need to look at what value is selected in the first combobox1 and run the code every time combobox1 changes its values. For this we will use the Combobox_AfterUpdate event.

From the left dropdown, select combobox1. From the right dropdown menu, select AfterUpdate. We can also use the Change event but we will stick to AfterUpdate in the article.

Now write the below code:

Private Sub ComboBox1_AfterUpdate()

Select Case ComboBox1.Value

 Case "India":

  states = Array("Delhi", "UP", "UK", "Gujrat", "Kashmir")

 Case "Nepal":

  states = Array("Arun Kshetra", "Janakpur Kshetra", "Kathmandu Kshetra",_

                "Gandak Kshetra", "Kapilavastu Kshetra")

 Case "Bhutan":

  states = Array("Bumthang", "Trongsa", "Punakha", "Thimphu", "Paro")

 Case "Shree Lanka":

  states = Array("Galle", "Ratnapura", "Colombo", "Badulla", "Jaffna")

End Select

  

ComboBox2.List = states

  

End Sub

Here we have used a select case statement. The select case statement is good when we want to see what value is chosen from many values. I have explained it here in detail.

To store the value inserted by the user use the submit button. Write the below code in the command button submit to save the country and state selected by the user on the worksheet.

Private Sub CommandButton1_Click()

     country = ComboBox1.Value

     State = ComboBox2.Value

 ThisWorkbook.Worksheets("sheet1").Range("G1") = country

 ThisWorkbook.Worksheets("sheet1").Range("H1") = State

Unload Me

End Sub

Now to show the userform, insert a button on the worksheet and write the below code. Or you can use a simple module to show the userform.

Sub load_userform()

UserForm1.Show

End Sub

Now run load_userform code.

How does it work?

When you run the sub that has code userform.show, the VBA runs userform_initialize events immediately after it runs userform.show command. In the userform_intialize event we have initialized the first combobox that has a list of countries. Afterwards the form is shown to the user.

Now when the user selects any value from the first combobox the event combobox1_AfterUpdate event runs. This event contains the code to check what value is selected by the user in combobox1 and based on that value, it sets the states array and initializes the combobox2 values with states array.

So yeah guys, this is how you create a cascading combobox in VBA userform. I hope I was explanatory enough and the article served its purpose. If you have any doubts regarding this article or any VBA topic, ask me in the comments section below

Leave a Reply

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