Excel VBA ComboBox with Control Source Property: 2 Methods

Method 1 – Using ComboBox Property

Step 1: Creating VBA UserForm With ComboBox in Excel

Create a UserForm with ComboBox. To create a Userform containing ComboBox, go through the following steps.

Go to the Developer tab in the ribbon and click on Visual Basic or press ALT+F11 from your keyboard to open Visual Basic.
Go to Insert → UserForm. A UserForm will be created.

Click on the ComboBox icon and drag it into the UserForm.

To get a clear view, watch the following demonstrative video.

Step 2: Select VBA ComboBox List from Range of Excel Worksheet

Input the range of data in the ControlSource and RowSource property of ComboBox.

Double click on the created ComboBox.
Set ControlSource as E5 and RowSource as B5:C14. Click Run.

Step 3: Use VBA ComboBox ControlSource Property to Store Value from Combobox to Certain Cell

A UserForm1 named dialog box will appear. Select your preferred Employee Name and enter. You will see that your selected data will be shown in cell E5. To get a clear understanding, you can follow the video demonstration below.

Method 2 – Using Private Sub to Store Value from Combobox to Certain Cell

Show another way of using ControlSource Property to populate a ComboBox with data from a worksheet with Excel VBA. Use a private sub to store value from ComboBox to a certain cell.

Create a new UserForm with a ComboBox like the previous method. Double-click on the UserForm to insert a private sub named UserForm_Initialize. We attached a step-by-step video for your better understanding.

Insert the following code and press Run.

Private Sub UserForm_Initialize()
  Dim items(1 To 10) As String
    items(1) = "Analyst II"
    items(2) = "Engineering Manager"
    items(3) = "Network Administrator"
    items(4) = "Field Engineer"
    items(5) = "Data Analyst"
    items(6) = "HRIS Analyst"
    items(7) = "Manager"
    items(8) = "Network Architect"
    items(9) = "Systems Analyst"
    items(10) = "Director"
    ComboBox1.List = items
    ComboBox1.ControlSource = "E5"
End Sub

Code Breakdown:

Private Sub UserForm_Initialize()

This line defines a private sub-procedure named UserForm_Initialize. This procedure is called automatically when the user form is loaded.

Dim items(1 To 10) As String

This line declares an array named items of type String with 10 elements.

items(1) = "Analyst II"
items(2) = "Engineering Manager"
items(3) = "Network Administrator"
items(4) = "Field Engineer"
items(5) = "Data Analyst"
items(6) = "HRIS Analyst"
items(7) = "Manager"
items(8) = "Network Architect"
items(9) = "Systems Analyst"
items(10) = "Director"

These lines populate the items array with job titles.

List = items

This line sets the List property of a combo box named ComboBox1 to the items array, populating the combo box with the job titles.

ControlSource = "E5"

This line sets the ControlSource property of ComboBox1 to “E5“. This means that when a job title is selected in the combo box, the value will be stored in cell E5 of the worksheet.

Here is a full demonstrative video of the process and final output after running the VBA macro.

Frequently Asked Questions

How to set RowSource for ComboBox in VBA?

In VBA, you can set the RowSource property of a ComboBox to populate the list of items dynamically from a range on a worksheet. Here’s an example code to set the RowSource property of a combo box named ComboBox1 to a range of values in column A of a worksheet:

ComboBox1.RowSource = "Sheet1!A1:A10"

In this example, Sheet1 is the name of the worksheet that contains the range of values, and A1:A10 is the range of cells that contain the values.

What is the control source for a combo box in access?

In Microsoft Excel, the ControlSource property for a ComboBox is the field or expression that the ComboBox is bound to. The control source determines the data that is displayed in the ComboBox, as well as the data that is stored when a selection is made.

How do I program a ComboBox in Excel VBA?

Yes, you can easily program a ComboBox in Excel using VBA code. Hopefully, the attached article from ExcelDemy will make your job easier and more efficient.

Key Takeaways from Article

In this article, I have shown how to create a UserForm with ComboBox.
Chosen real life dataset for better understanding.
Focusing on how to use the ControlSource property to populate a ComboBox with data using Excel VBA.
Explained 2 different approaches with VBA code.
Provide solutions to frequently asked questions of readers.
Overall focused on using VBA code to populate a ComboBox with data along with the ControlSource property.

Download Practice Workbook

You can download the practice workbook from here:

Excel VBA ComboBox with Control Source Property.xlsm

Get FREE Advanced Excel Exercises with Solutions!