Combo Box in VBA
Combo Box is very similar to the drop-down list we have in an excel worksheet, with the combo box we can store pre-determined values, so that users make the selection from the list available from the combo box. In this article, we will show you how to use a combo box in VBA coding in detail. Read the entire article to get the taste of a combo box in excel VBA.
Combo Box is generally used along with user forms as part of getting the input from the users.
User forms are useful but having other tools on the user form is what makes the user form so special. One of the tool that we often use as a tool for user form is “ComboBox”.
Creating Combo Box with Excel Worksheet
First, we will see how to use Combo Box with the worksheet. Open any of the worksheet in the excel workbook, go to the Developer tab and under this tab we have a tool called “Insert”. Click on this and under this we have two options “Form Controls” & “Active X Controls”.
From “Active X Controls” choose “Combo Box”.
Now you can draw this object on any of the worksheets.
Right-click on the combo box and choose the “Properties” option.
When you choose properties it will open up huge list of properties of a combo box.
For this combo box, we are going to give a list of department names so change the name property of the combo box to “DeptComboBox”.
Now this combo box will be referred by the name “DeptComboBox”. We need to give pre-determined department names, so here I have a list of department names.
Now we need to add these values to the combo box list, we can do this in two ways, through coding or through name manager.
First, we will see through the coding way.
#1 – Through Coding
Double click on the combo box and it will take you to the VBA macro procedure.
But we need to see these department names when the workbook opens up, so double click on “ThisWorkbook”.
From the drop-down list choose “Workbook”.
From options choose “Open”.
Now it will create a blank like the below one.
Inside this macro enter the below code.
Private Sub Workbook_Open() With Worksheets("Sheet1").DeptComboBox .AddItem "Finance" .AddItem "Marketing" .AddItem "Merchandising" .AddItem "Operations" .AddItem "Audit" .AddItem "Client Servicing" End With End Sub
Ok, now save and close the workbook, when you reopen the workbook we could see the department names in it.
#2 – Using UserForm
Another way of adding values to the combo box is by using user form. First, give a name to department name cells.
Now the new user form is created.
Next to the user form, we can see “Toolbox” from this toolbox we can insert “Combo Box”.
Now the combo box is embedded to user form, in this method to open the properties option select the combo box and press the F4 key to open the properties window.
Scroll down the properties tab and choose “Row Source”.
For this “Row Source” enter the name that we had given to department name cells.
Now this combo box holds the reference of the name “Department”.
Now run the user form by using the run button.
Now we can see a list of department names in the combo box on the user form.
Practical Example of Combo Box in Excel VBA
Practically user form associated with a combo box, text box, and many other tools. We will create a simple data entry user form with a text box & combo box.
Create a user form like the below.
Create to Command Buttons.
Double click on the “SUBMIT” button it will open up below macro.
Inside this macro add below code.
Private Sub CommandButton1_Click() Dim LR As Long LR = Cells(Rows.Count, 1).End(xlUp).Row + 1 Cells(LR, 1).Value = TextBox1.Value Cells(LR, 2).Value = ComboBox1.Value End Sub
Now double click on the “CANCEL” button and add the below code.
Now in the worksheet create a template like the below.
Now run the user form and it will open up like this.
Enter the employee name and pick the department name from the combo list.
Click on the SUBMIT button and see the magic.
We got the entered values in the table format we have created.
Things to Remember
- VBA COMBO BOX is a tool similar to the drop-down list in an Excel worksheet,
- COMBO BOX has its own properties as well.
- Adding values to list comes in two ways one is coding way and another one is range name reference.
- COMBO BOX is usually part of the user form.
This has been a guide to VBA Combo Box. Here we discuss how to insert and use a combo box using VBA coding in excel along with the downloadable template. Below you can find some useful excel VBA articles –
- Splitting String into Array Examples
- Password Protect VBA Project
- Excel VBA String Compare
- Web Scraping in VBA
- VBA Square Root Function
- Examples of List Box in Excel VBA
- Examples of Name Box in Excel
- VBA InputBox Examples
- Examples of Checkbox in Excel