Excel VBA ComboBox
ComboBox is the user form feature in VBA, they are different from the text boxes as text boxes are used to contain only text and we allow user to input any type of data, but by using combo boxes we limit user for the responses type we desire thus the data is in orderly fashion, it is similar to the list data validation in excel.
ComboBox can be compared to a drop-down list in excel, in worksheets we used data validation to provide a drop-down but in VBA there is a user form feature where it is used to provide a drop-down in any user form, but if we want to use a combo box in excel we can also access it from the developer section from there we can create combo boxes for individual or multiple cells.
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. 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 tools that we often use as a tool for user form is “ComboBox”.
Top 2 Ways of Creating a VBA ComboBox
#1 – Using Direct Coding
First, we will see how to use Combo Box with the worksheet. Open any of the worksheets 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 Active X Controls & Form Controls in excel .
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.
Double click on the Combobox 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 ComboBox is by using user form. First, give a name to cells as “Department”.
Go to Visual Basic Editor and insert User Form from the INSERT option.
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.
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 two 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 values entered in the table format we have created.
Things to Remember
- COMBO BOX has its own properties as well.
- Adding values to list comes in two ways one is a 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 ComboBox. Here we discuss how to insert and use a Combobox using VBA coding in excel along with the downloadable template. Below you can find some useful excel VBA articles –