Dependent Combo box in Excel VBA

Adding the particular dependent box in the created User form with the help of the Microsoft Excel VBA is considered essential and crucial in Microsoft Excel.

Here, in this tutorial, we will discover the following concepts in detail, which are as follows:

1. What do you mean by Excel VBA?
2. How do you create the Userform and add the Dependent Combo box?

What do you mean by the term Excel VBA?

The word VBA is abbreviated as the Visual Basic for Applications. And the term Excel VBA is defined to be the most acceptable type of Microsoft's programming language that can be used for Microsoft Excel and the other various Microsoft office programs; some of them are as follows:

• Microsoft Word.
• Microsoft PowerPoint.

And it was noted that the particular Office suite programs share a standard programming language.

How do you create the Userform and add the dependent combo box?

In the respective Microsoft Excel Virtual Box for Applications, there exists a valuable tool named Userform. This feature is considered as the practical way of getting the data as well as the information from the respective individual and the User.

What do you understand by the Userform in VBA?

“Userform is a box similar to a wizard box or a dialogue box in which an individual asks the respective users to enter (input) the data. They are encapsulated with some specific types of instructions too.”

Furthermore, the VBA Userform is already present in Microsoft Excel and can be efficiently created by the developers. To access or make use of the VBA Userform, an individual firstly needs to actively activate the developer tab from the options menu. Once activated, an individual can move ahead in Excel VBA to create the Userform. The respective or the created Userforms are used to store the data and the information in Microsoft Excel.

It is very similar to the Google forms sponsored by Google, in which we have a specific set of instructions and ask the User to input the data as per instructions passed to them by an individual to carry out the particular types of operations.

Besides all these, the Userform is extensively used by everyone in this modern world of computer technology. In the respective type of the Userform, the data entered by the particular User get automatically stored in the corresponding column of Microsoft Excel.

# Example for creating the Microsoft Excel VBA Userform

Let's start from the basic and then move to its advanced versions,

• First, in the Microsoft Excel Worksheet/spreadsheet, an individual should drag to the Developer tab present toolbar and then click on the Visual Basic editor under the Developer tab options as shown in the below attached screenshots.

• Now, one should insert the Userform to the respective blank screen by clicking on the insert button and, under that, selecting the option "UserForm," as shown in the below attached screenshots.

• After performing the above steps, a new wizard box appears on the screen. It is primarily blank and hence terms to one's blank UserForm on which various buttons can be added to perform multiple operations on the Excel sheets as shown in the below attached screenshots.

• As soon as an individual creates the UserForm, the name of the UserForm will be named "UserForm1" because it is called by default, and an individual has not given any particular character to the created UserForm as shown in the below attached screenshots.

•  And if an individual wants to change the name of the UserForm created by them according to their own choice of name, then they can do it very quickly by just clicking on the properties on the left-hand side of the screen.

• Suppose we have changed the name to "Harish records", and now the Userform name will be displayed as the Harish records rather than the UserForm 1.
  Now, as seen in the below-attached screenshot, our UserForm is named Harish records as shown in the below attached screenshots.

• Now, we want to change the color of the UserForm to some specific color. For this, we have to go to the property’s toolbox, where we have the dropdown option already provided named the "back color", and from that, we can choose any color we want as shown in the below attached screenshots.

• Once we have selected the option for the back colour, then the colour for the Userform, which is named the "Harish Records", will be changed as follows,
• And now, the question arises of how to run this UserForm. And the answer is straightforward, and it should be made sure by the individual's that they clicked on the Userform by itself and then clicked on the run button (Alt+f5) as displayed in the attached screenshots below as soon as an individual clock on the run button it is visible that the particular blank form gets on the Microsoft excel screen.

And the output is simple as no buttons are added to the UserForm, and it is better to close it now.

Now, we will see how to add buttons to the created UserForm, "Harish Records", with the help of Example 2, respectively.

Moving on to discussing how to add the dependent combo box in Excel VBA,

The logic for Creating the Dependent Combo box in the VBA

To create a conditional combo box in the VBA (Virtual Basic for Applications), an individual or the developer needs to initialize the particular combo box in the event of the individual element called the "source element."

Understanding these with the help of the example, assuming that the combo box may depend upon the radio button group, then in these particular cases, the code associated with it to load the dependent combo box should run as soon as possible (immediately) after other individual or the User selects that respective radio buttons for performing some actions.

In the other case, considering that one combo box depends upon the other combo box, the dependence on the combo box must be loaded at every instance of the time, and the value in the combo box gets changed respectively.

The ComboBox depends upon the other ComboBox: For this, an individual needs to create the two ComboBox. From which the first ComboBox is used to contain a few names of the countries such as India, South Africa, Nepal, Sri Lanka, etc. And the next ComboBox is used to hold the names of some of the states, such as Uttar Pradesh, Delhi, Kashmir, Uttrakhand, Gujarat, etc... If the User makes changes to the first combo box, which is holding the names of the countries. The list in the other ComboBox also gets changed accordingly, as shown in the below attached screenshots.

Now let us create a Userform which explicitly contains the two ComboBox and labels as Countries and the States, respectively, and a command button with the two ComboBox to submit the given input by the particular User.

• And in these, the first Combobox is assigned with the list of the different countries names. It does not depend on any various kinds of value, so we will load it in the form_initilaize event as an individual does it for the basic initialization of the ComboBox, respectively.
• And making double click on the respective UserForm will effectively open the area called the “coding area” in the Object of the UserForm.
• Now with the help of the left dropdown menu, an individual will select the user form. And from the service of the right dropdown menu, choose initializations.
• Moreover, the empty sub-name UserForm_Initialize () gets inserted. And things written in that particular sub will be executed effectively before the respective UserForm display it.

So, the initialization codes for the ComboBox that the individual can write are as follows:

Private Sub UserForm_Initialize ()
Countries = Array (“India”, “South Africa”, “Nepal”, “Sri Lanka”)
UserForm1. ComboBox1.List = states
End Sub 

The above code is termed to be the first ComboBox and is initialized. Whenever an individual tries to load the respective UserForm, the first ComboBox will get ready with the name "countries."

And moving further, to load the second Combobox, an individual needs to look at the particular type of the value selected in the first combobox1, and an individual will run the code every time combobox1 changes its defined value. And for this, an individual will use the Combobox_AfterUpdate event respectively. Select the combobox1 from the left dropdown, and from the right dropdown menu, an individual should select AfterUpdate, and alternatively, an individual can also make use of the Change Event.

Codes which are associated with this ComboBox are mentioned below:

Private Sub ComboBox1_AfterUpdate ()
Select Case ComboBox1.Value
Case “India”:
                      States = Array (“Uttar Pradesh”, “Delhi”, “Kashmir”, “Uttrakhand”, “Gujarat”)
Case “Nepal”:
                            States = Array (“Gandak Kshetra”, Kathmandu Kshetra”, “Arun Kshetra”, Janakpur Kshetra”)
Case “South Africa”:
                             States = Array (“Eastern Cape”, “Free State”, “Limpopo”, “Gauteng”)
Case “Sri Lanka”
                  States = Array (“Ratanpura”, “Colombo”, “Jafna”, “Badulla”)
End Select
ComboBox2.List=states
End Sub

To store the particular individual's value, use the submit button. An individual should write the code in the respective command button to save the country and the state selected by the individual on the separate worksheet.

Private Sub CommandButton1_Click ()
Country=ComboBox1.Value
State=ComboBox2.Value
ThisWorkbook.Worksheets (“sheets1”). Range (“G1”) = country
ThisWorkbook.Worksheets (“sheet1”). Range (“H1”) =states
Unload Me
End Sub

Now, to show the UserForm, an individual should insert a button on the worksheet and will write the respective code below. The Simple modules for the UserForm are as follows:

Sub load_userform ()
UserForm1.Show
End Sub

Essential Things to be always remembered

The various crucial points which are associated with the working of the dependent combo box in the Microsoft Excel VBA (Virtual Basic for Applications) are as follows:

1. Userforms are inbuilt ones, or they might be created very efficiently by the developer in the Virtual Basic for Applications (VBA).
2. Suppose an individual wants to change the properties associated with the created UserForm. In that case, they can easily do it with the help of the option that is the “Wizard Box” available in the VBA (Virtual Basic for Applications).
3. And the most important thing to note is that the respective UserForm needs to have some specific types of buttons to fetch the required amount of data from the particular individual effectively.
4. Individuals should select the user form if they want to run the UserForm.