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

What Is ComboBox in Excel VBA?

The ComboBox is a particular type of UserForm, distinct from the TextBox which can only store text data. A ComboBox let’s users choose an option from a drop-down menu list, which restricts input to the appropriate response type. Moreover, we get organized data in a list using ComboBox in our worksheets.

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

We can use either UserForms or VBA code to create a ComboBox, and will demonstrate both approaches below. We’ll use the following dataset containing the names of salespersons and their organizations.

Method 1 – Create Combobox with RowSource Property Through Excel UserForm

In VBA or a macro, the RowSource property sets up the Properties window. A ComboBox can use a single column like the one shown below as an input list.

Steps:

Select the range B5:B9 and name the array as Salesperson.

Go to the Developer tab and click Visual Basic.

A Visual Basic window will appear.

Click Insert > UserForm to create a UserForm.
Go to View > Toolbox, add a ComboBox, and label it as Salesperson.

Create a TextBox and name it Company Name.
Create 2 CommandBoxes labelled Insert and Cancel.

These CommandBoxes will take instructions and guide the code accordingly.

Click on the ComboBox and press F4 on your keyboard.
The Properties dropdown box will appear.
Scroll down to RowSource and label it as Salesperson.

Remember to use the same name reference while calling the property name.

Double-click on the command box and enter or copy and paste the following VBA code in the module box:

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

Press the green Run button to execute the code.

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.

The data appears in the dataset sequentially.

In the same way, run the code for each row.

Method 2 – Use RowSource Property in VBA Code to Insert ComboBox

Generally, RowSource specifies the source of a list in a ComboBox or ListBox, and approves the worksheet ranges provided by Excel. Like in Method 1, we will also fill a ComboBox with a list using VBA code with the RowSource property. However, we won’t create any UserForms in this method. Instead, we will use direct coding.

Steps:

Go to Developer > Insert > ComboBox.

A ComboBox appears in the dataset.

Right-click on the box to open the context menu.

Click Properties.

The Properties dropdown box pops up.

In the Name box, type Salesperson.

Double-click on the ComboBox and enter 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