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.
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.
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.
Related Articles:
Getting Started With Excel VBA UserForms| I will explain how to create a form in excel, how to use VBA toolbox, how to handle user inputs and finally how to store the user inputs. We will go through these topics using one example and step by step guide.
VBA variables in Excel| VBA stands for Visual Basic for Applications. It is a programming language from Microsoft. It is used with Microsoft Office applications such as MSExcel, MS-Word and MS-Access whereas VBA variables are specific keywords.
Excel VBA Variable Scope| In all the programming languages, we have variable access specifiers that define from where a defined variable can be accessed. Excel VBA is no Exception. VBA too has scope specifiers.
ByRef and ByVal Arguments | When an argument is passed as a ByRef argument to a different sub or function, the reference of the actual variable is sent. Any changes made into the copy of the variable, will reflect in the original argument.
Delete sheets without confirmation prompts using VBA in Microsoft Excel | Since you are deleting sheets using VBA, you know what you are doing. You would like to tell Excel not to show this warning and delete the damn sheet.
Add And Save New Workbook Using VBA In Microsoft Excel 2016| In this code, we first created a reference to a workbook object. And then we initialized it with a new workbook object. The benefit of this approach is that you can do operations on this new workbook easily. Like saving, closing, deleting, etc
Display A Message On The Excel VBA Status Bar| The status bar in excel can be used as a code monitor. When your VBA code is lengthy and you do several tasks using VBA, you often disable the screen update so that you don’t see that screen flickering.
Turn Off Warning Messages Using VBA In Microsoft Excel 2016| This code not only disables VBA alerts but also increases the time efficiency of the code. Let’s see how.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
How to use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
