When you use Microsoft Excel, I am pretty sure you often use ComboBoxes in UserForm. Excel provides some unique VBA tools for developing combo boxes. In this article, we will use Excel VBA ComboBox with Control Source Property using data from a worksheet. Let’s get started!
The above video shows the overview of using Excel VBA combobox with the control source property.
Overview of VBA ComboBox.ControlSource Property in Excel
In Excel VBA, ControlSource is used to bind a control on a userform or worksheet to a data source. It allows us to determine the data displayed in a control. This property allows us to present and modify data that is linked to a field which is present in a table, query, or SQL statement. Also, we can show the expression’s result or a String by setting the ControlSource property of a control.
For example, if you have a ComboBox on a userform and you want to populate it with data from a worksheet range, you can use the ControlSource property to specify the range of cells as the data source for the ComboBox. This allows you to easily update the ComboBox items by updating the worksheet range.
Syntax
Expression. ControlSource
Expression: A variable that represents a ComboBox object.
Remarks
ControlSource has the following properties.
| Setting | Description |
|---|---|
| A field name | The control is connected with a specific field present in a table, query, or SQL statement. It displays the field’s data.
Changes to the information within the control have an impact on the field’s data. (To make the control read-only, we must set the Locked value to Yes.) Suppose you select a control in Microsoft Excel that is associated with a field containing a data type which is Hyperlink. Control will direct you to the URL indicated in the hyperlink address by clicking on that. |
| An expression | Control shows data from an expression and can be edited, but any changes made will not be saved to the database. |
When you create forms and reports in Microsoft Excel, they act like “windows” that let you see into your database. To determine what data appears in a form or report, you set the RecordSource property to a table, query, or SQL statement. Then, you can use ControlSource to indicate which field or statement the data in the form or report should be based on.
It’s important to note that for reports, the ControlSource property only applies to report group levels, whereas for forms, it applies to all controls.
If ControlSource property settings are expressions, you can only read the value that is displayed. Moreover, this value will not be saved in the database. For example, you can go through the following properties.
| Sample setting | Description |
|---|---|
| LastName | Control can be used to display the information from the LastName field. Also, when creating a report, the data can be organized and displayed based on the last name using a group level. |
| =Date( )+7 | In the control, this expression shows a date seven days from today. |
| =DatePart(“q”,ShippedDate) | This expression displays the fourth quarter of the delivered date.
When you generate a report with a group level, Access will group and present the data based on the quarter of the delivery date. |
Excel VBA ComboBox with Control Source Property: 2 Examples
In the following sections, we will show you two suitable examples with detailed explanation. I hope it will give you great understanding of using Excel VBA ComboBox with Control Source Property.
1. Using ComboBox Property
In this part of the article, I am going to demonstrate how to create an Excel UserForm with ComboBox. Later on, I will demonstrate how to fill the ComboBox with a list from the Range of Excel Worksheet. I will also demonstrate how to store values from a combobox to a cell using VBA ComboBox ControlSource property.
Step 1: Creating VBA UserForm With ComboBox in Excel
First, let’s 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, you can just press ALT+F11 from your keyboard to open Visual Basic.
- Then go to Insert → UserForm. A UserForm will be created.
- Now, 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
Now, I am going to input the range of data in the ControlSource and RowSource property of ComboBox. Let’s go through the below steps accordingly.
- First double click on the created ComboBox.
- Then 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
Now 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 below video demonstration.
2. Using Private Sub to Store Value from Combobox to Certain Cell
In this part of the article, I will show another way of using ControlSource Property to populate a ComboBox with data from a worksheet with Excel VBA. Here, we will use a private sub to store value from ComboBox to a certain cell.
Now create a new UserForm with a ComboBox like the previous method. Then double-click on the UserForm to insert a private sub named UserForm_Initialize. Here I have attached a step-by-step video for your better understanding.
Finally, 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:
Here,
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 StringThis 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 = itemsThis line sets the List property of a combo box named ComboBox1 to the items array. This populates 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 whole process and final output after running the VBA macro.
Read More: How to Add Item to ComboBox with VBA in Excel
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:
Conclusion
In this article I have tried to cover how you can easily use Excel VBA ComboBox with Control Source Property to populate a ComboBox with data from a worksheet. This will help you work faster and more efficiently. I hope you found this article informative and enjoyable. If you have any questions, comments, or recommendations, please let me know in the comment section.
Related Articles
- How to Add ComboBox in Excel
- Excel VBA ComboBox Value
- Excel VBA ComboBox: Important Properties to Explore
- Create ComboBox with RowSource in Excel VBA
- How to Get Excel VBA Combobox with RowSource Named Range
