Create ComboBox with RowSource in Excel VBA (2 Easy Ways)

Microsoft Excel is a powerful software. We use Excel functions and features for our educational, business, and other daily life purposes. Furthermore, Excel provides some unique VBA tools to develop ComboBox. This article is based on the guidelines for creating an Excel UserForm with ComboBox with examples. Furthermore, it demonstrates how to fill the ComboBox with lists using the RowSource property. Using VBA, we add these lists to the ComboBox on that page by looping through a named range. Therefore, let’s learn 2 easy ways to create ComboBox with RowSource in Excel VBA.

What Is ComboBox in Excel VBA?

The ComboBox is a particular type of UserForm. The ComboBox tool is distinct from the TextBox feature since the latter can only store text data. Although we let users enter any data, we can restrict them to the appropriate response type by utilizing ComboBox. Moreover, we get organized data in a list using ComboBox in our worksheets. Users can choose an option from a drop-down menu list using ComboBoxes.

How to Create ComboBox with RowSource in Excel VBA: 2 Easy Ways

Both UserForms and VBA codes allow us to create ComboBox. You will discover how to construct and deal with ComboBoxes in Excel worksheets and VBA in this lesson. Constructing ComboBox in Excel can be a long process if do not know the right methods. In this article, I will show you 2 effective methods to create ComboBox with RowSource in Excel. For instance, we take a dataset that refers to the names of salespersons and their organizations.

1. Create Combobox with RowSource Property Through Excel UserForm

In this method, we will develop a ComboBox with UserForm by applying the RowSource property. In VBA or macro, the RowSource property sets up the Properties window. A ComboBox can have a single column like the one given below. Also, populate the dataset with the salesperson’s name. So, follow the steps carefully to Create Combobox with RowSource in Excel VBA.

Steps:

First, select the range B5:B9 and name the array as Salesperson.

Next, go to the Developer tab and click Visual Basic.

As a result, a Visual Basic window will appear.
Now, tap Insert > UserForm to create a UserForm.
Afterward, go to View > Toolbox and add a ComboBox, and label it as Salesperson.

Further, create a TextBox and name it Company Name.
Finally, create 2 CommandBox labeling Insert and Cancel.
The CommandBox will take the instructions and guide the code in the right direction.

Later, tap on the ComboBox and press F4 on your keyboard.
Eventually, the Properties dropdown box will appear.
Here, scroll down to RowSource and level it as Salesperson.
Hence, we set the RowSource Property to Salesperson. Remember to use the same name reference while calling the property name.

Meanwhile, double-click on the command box and type the following VBA code in the module box. You can also copy and paste the code into your module.

Private Sub CommandButton1_Click()
Dim LR As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row + 4
Cells(LR, 3).Value = TextBox1.Value
Cells(LR, 2).Value = ComboBox1.Value
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub

Finally, hit the green Run button to execute the code.

Lastly, the UserForm box appears in the dataset.
Input data in the Company Name box and tap the dropdown icon below.
Select the corresponding Salesperson and press Insert.
See the picture below to understand better.

Thus, the data appears in the dataset sequentially.
In the same way, run the code for each row.

2. Use RowSource Property in VBA Code to Insert ComboBox

Generally, RowSource specifies the source giving out a list in a ComboBox or ListBox. The RowSource property approves the worksheet ranges provided by Excel. In this method, we will fill a ComboBox with a list using VBA code with the RowSource property. However, we will create any UserForms in this method. Instead, we will use direct coding to implement this. To do so, follow the steps.

Steps:

To begin with, go to Developer > Insert > ComboBox.

Subsequently, a ComboBox appears in the dataset.
Right-click on the box and open a context menu.

Now, click Properties.

Consequently, the Properties dropdown box pops up.
Here, in the Name box, type Salesperson.

Later, double-click on the ComboBox and type the following VBA code.

Private Sub Workbook_Open()
With Worksheets("RowSource & VBA").Salesperson
.AddItem "Andy Teal"
.AddItem "Robert Walters"
.AddItem "Susan W. Eaton"
.AddItem "Kim Ralls"
.AddItem "Kathie Flood"
End With
End Sub

Hit the green Run button.

Hence, the desired ComboBox appears in the dataset.
Tap the dropdown icon to the lists of datasets.

Download Practice Workbook

You can download this workbook to practice yourself.

ComboBox with RowSource.xlsm

Conclusion

In conclusion, we have discussed 2 effective methods to create ComboBox with RowSource in Excel. Not to mention, our ExcelDemy website shows various simple problem-solving methods like this. Please leave any further queries or recommendations in the comment box below.

Get FREE Advanced Excel Exercises with Solutions!