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."

#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.

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 a huge list of combo box properties.

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

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.

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

Double click on the combo box, which 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 dropdown list, choose "Workbook."

From the options, choose "Open."

Now it will create a blank like the one below.

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

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

This topic opens up many avenues for further exploration. If you’re intrigued by these concepts, you might find this Automated Data Entry Form Using VBA Course provides additional insights and hands-on experience.

#2 - Using UserForm

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

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

Now, it has created the new UserForm.

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

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.

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

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

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

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

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

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 two "Command" buttons.

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

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

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

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

Now, run the UserForm. 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.

• 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.