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.
Read More: How to Use VBA to Populate ComboBox List from Range in Excel
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- Hit the green Run button.
The desired ComboBox appears in the dataset.
- Tap the dropdown icon to display the list.
Download Practice Workbook
Related Articles
- How to Add Item to ComboBox with VBA in Excel
- Excel VBA ComboBox Value
- How to Use Excel VBA ComboBox with Control Source PropertyÂ
- Excel VBA to Populate ComboBox from Dynamic Range
- Excel VBA ComboBox: Important Properties to Explore
- How to Get Excel VBA Combobox with RowSource Named Range
