VBA ComboBox

Last Updated :

-

Blog Author :

Jeevan A Y

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

Excel VBA ComboBox

Combo Box is the UserForm feature in VBA. They are different from the text boxes as text boxes used to contain only text. We allow the user to input any data, but by using combo boxes, we limit users to the desired response type. Thus, the data is in an orderly fashion. It is similar to the list data validation in Excel.

Combo Box can be compared to a dropdown list in Excel in worksheets. We used data validation to provide a dropdown. But in VBA, a UserForm feature provides a dropdown in any UserForm. But, if we want to use a combo box in Excel, we can 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 dropdown list we have in an excel worksheet. With the combo box, we can store predetermined values so that users make the selection from the list available from the combo box. The combo box is generally used with UserForms to get the users' input.

UserForms 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 UserForm is "Combo Box."

Table of contents
VBA-Combo-Box

Top 2 Ways of Creating a VBA ComboBox

#1 - Using Direct Coding

First, we will see how to use Combo Box with the worksheet. Next, open any worksheets in the Excel workbook, and go to the Developer tab. Under this tab, we have a tool called "Insert." Click on this, and under this, we have two options Active X Controls and Form Controls in excel.

VBA Combo box Example 1

From "Active X Controls," choose "Combo Box."

VBA Combo box Example 1-1

Now you can draw this object on any of the worksheets.

VBA Combo box Example 1-2

Right-click on the combo box and choose the "Properties" option.

VBA Combo box Example 1-3

When you choose properties, it will open up a huge list of combo box properties.

VBA Combo box Example 1-4

We will give a list of department names for this combo box, so change the name property of the combo box to "DeptComboBox."

VBA Combo box Example 1-5

This combo box will be referred to by the name "DeptComboBox." We need to give pre-determined department names, so here I have a list of department names.

VBA Combo box Example 1-6

Now we need to add these values to the combo box list. We can do this in two ways, through coding or name manager.

VBA Combo box Example 1-7

Double click on the combo box, which will take you to the VBA macro procedure.

VBA Combo box Example 1-8

But we need to see these department names when the workbook opens up, so double click on "ThisWorkbook."

VBA Combo box Example 1-9

From the dropdown list, choose "Workbook."

VBA Combo box Example 1-10

From the options, choose "Open."

VBA Combo box Example 1-11

Now it will create a blank like the one below.

VBA Combo box Example 1-12

Inside this macro, enter the below code.

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
VBA Combo box Example 1-13

Now save and close the workbook. When you reopen the workbook, we can see its department names.

VBA Combo box Example 1-7

#2 - Using UserForm

Another way to add values to the combo box is using the UserForm. First, give a name to cells as "Department."

VBA Combo box Example 1-14

Next, enter Visual Basic Editor and insert "UserForm" from the "INSERT" option.

VBA Combo box Example 1-15

Now, it has created the new UserForm.

VBA Combo box Example 1-16

Next to the UserForm, we can see "Toolbox" from this toolbox. Then, finally, we can insert "ComboBox."

VBA Combo box Example 1-17

Now, the combo box is embedded in the UserForm. To open the properties option, select the combo box and press the F4 key to open the properties window.

VBA Combo box Example 1-18

Scroll down the "Properties" tab and choose "RowSource."

VBA Combo box Example 1-19

For this "RowSource," enter the name we had given to department name cells.

rowsource Example 1-20

Now, this combo box holds the reference of the name "Department."

Now, run the UserForm by using the "Run" button.

run userform Example 1-21

Now, we can see a list of department names in the combo box on the UserForm.

VBA Combo box Example 1-22

Practically UserForm is associated with a combo box, text box, and many other tools. Therefore, we will create a simple data entry UserForm with a text box and combo box.

Create a UserForm like the one below.

create userform Example 1-23

Create two "Command" buttons.

command button Example 1-24

Double click on the "SUBMIT" button. It will open up below the macro.

macro code Example 1-25

Inside this macro, add the below code.

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
Submit button Example 1-26

Now, double-click the "CANCEL" button and add the code below.

Cancel button Example 1-27

Now, in the worksheet, create a template like the one below.

Excel worksheet Example 1-28

Now, run the UserForm. It will open up like this.

created userform Example 1-29

Enter the employee name and pick the department name from the combo list.

department Example 1-30

Click on the "SUBMIT" button and see the magic.

VBA Combo box Example 1-31

We got the values entered in the table format we have created.

Things to Remember

  • The COMBO BOX has its properties as well.
  • Adding values to the list comes in two ways. One is a coding way, and another is a range name reference.
  • The COMBO BOX is usually part of the UserForm.