How To Create Dependable Combo Box

Create Dependable Combo Box in userform with the help of Dynamic Name Manager.

1.     Create a list with all the required data

2.     The Example is here:-

Column A is ‘All Regions”

Cell A2 name is Americas, So Column B has all the countries’ names which is in the Americas region.

Cell A3 name is Asia Pacific, So Column C has all the countries’ names which are in Asia Pacific region.

Cell A4 name is EMEA, So Column D has all the countries’ names which are in the EMEA region.

Logic: So when we run the userform, combo box 1 will show all regions’ name and if Americas is selected then combo box 2 will filter and display data from Column B and so on with another region

Now How To Create A Dynamic Name Range

In the Ribbon select FORMULAS tab

Now first give the range name to Column A which is All-Region. Select the range that you want to define the name. Now in Name Manager select Create from Selection. Or you can use the shortcut key Ctrl+shift+F3. Choose Top Row to make the column’s first name as Header.

Check if the Name Manager has done proper selection or not

Click Name Manager and you will able to see the name as well as the range that it has taken

Follow the above steps to create the name for Column B (Americas), Column C (Asia Pacific) & Column D (EMEA)

Note: The Name manager will automatic take underscore (_) if there is space between 2 words like   Asia Pacific.

See below:-

Now press alt+F11 to start VBA. Insert userform and design it.

¨    Take 2 Label and name as Region & Country.

¨    Take 2 combo box 

Now double click on userform to initialize it. Why? So that once the userform will be displayed, the combo box will automatically pick the defined name. Here Combo box 1 we are setting the column A name range i.e. All_Region

Private Sub UserForm_Initialize()

Me.ComboBox1.RowSource = "All_Region"

End Sub

Next, we need to write code for Combo box 1. Go back to userform and double click on the combo box 1 and write the following code. Logic: When there will be a selection from

 

Private Sub ComboBox1_Change()

Me.ComboBox2 = ""

Select CaseMe.ComboBox1

    Case"Americas"                                                       ‘Cell A2 name is Americas

        Me.ComboBox2.RowSource = "Americas"      ‘ The rowsource is nothing but the name range.

    Case"Asia Pacific"

        Me.ComboBox2.RowSource = "Asia_Pacific"

    Case"EMEA"

        Me.ComboBox2.RowSource = "EMEA"       

End Select   

End Sub

 

Now Run userform. Select Any region name from combo box 1 and go to combo box 2 to select the country.