Tanjim Reza
When working with Excel, ComboBox is a great tool to select and process data. You can easily select values from given ComboBox values. Now, it is a dire need to know about the uses and needs of VBA ComboBox values. In this article, I will show you 3 practical uses of VBA ComboBox value in Excel.
Download Practice Workbook
You can download our practice workbook from here for free!
How to Add a ComboBox in Excel
Say, you have several employees’ names. Now, you want to create a ComboBox containing the dropdown of those employees’ names and wanna select a particular employee. You can do this by adding a ComboBox in Excel. Follow the steps below to do this.
📌 Steps:
- First, go to the Developer tab >> Insert tool >> Combo Box (ActiveX Control option).
- Following, drag an area where you want the combo box inside the Excel file.
As a result, a combo box will appear in the selected place.
Now, after adding a combo box, generally, we need to perform three tasks in VBA ComboBox value, such as: adding values, removing values, and setting default values. Now, to learn about these uses and ways go through the article below.
2 Ways to Add Value to ComboBox in Excel VBA
The most important task with a combo box is to add values to the combo box. In this regard, you can accomplish this with single inputs inside the VBA code. Again, you can accomplish this using ranges that are present in Excel cells already.
1. Adding Values as Single Inputs
Say, the dataset is the same as before. You need to input the names of the employees that are given in cells B5:B9. You can do this by inputting the names as single values inside the code. Follow the steps below to do this.
📌 Steps:
- First, double-click on your added combo box.
- As a result, the code window of VB Editor will open for that particular sheet.
- Next, write the following code inside the code window and press Ctrl + S.
- Afterward, a Microsoft Excel dialogue box will appear. Click on the No button.
- As a result, the Save As dialogue box will appear.
- Following, choose the Save as type: option as .xlsm file and click on the Save button.
- Afterward, click on the Run button inside the code window.
Thus, you will see the values will be added into the combo box. And, the final outcome should look like this.
Read More: How to Add Item to ComboBox with VBA in Excel
2. Adding Values from Range
You can also use the cell range to add values in the ComboBox. Follow the steps below to accomplish this.
📌 Steps:
- At the very beginning, click on the combo box.
- Following, go to the Developer tab and click on the Properties tool.
- As a result, the Properties window will appear.
- Here, input the range B5:B9 in the ListFillRange option’s text box.
As a result, the values of B5:B9 cells will be input into the dropdown and the final result would look like this.
Read More: How to Use ListFillRange Property of ComboBox in Excel
Similar Readings
- How to Populate Excel VBA ListBox Using RowSource
- Use VBA to Populate ComboBox List from Range in Excel
- Use ComboBox to Get Selected Item Utilizing VBA
- How to Select First Item from ComboBox Using VBA in Excel
- Excel VBA ComboBox: Important Properties to Explore
How to Remove ComboBox Value in Excel VBA
Now, sometimes, it might happen that you need to remove the ComboBox values with VBA. Follow the steps below to do this.
📌 Steps:
- Similarly, as before, double-click on the combo box and go to the code window.
- Following, insert the following VBA code in the code window and click on the Run button.
Consequently, you will see that all the values are removed from the dropdown and the result should look like this.
Read More: How to Clear Items from VBA ComboBox in Excel
How to Set a Default ComboBox Value in Excel VBA
Another important task in this regard is to set a default value from the dropdown combo box. Say, you want to set a default value in cell D6 from the dropdown ComboBox. Go through the steps below to do this.
📌 Steps:
- Initially, choose the name that you want to set as default.
- Following, go to the Developer tab >> View Code window.
- Afterward, the VB Editor code window will appear.
- Following, insert the VBA code below and save the file.
- Last but not least, click on the Run button inside the code window.
- As a result, you will see that the selected name (Sarah here) is set on cell D6.
- Now, change the employee’s name and you will still see the previous name on cell D6 as default.
And the final outcome should look like this.
Note:
If you want to change the default value on cell D6, you have to select your desired name and run the code again. Then, the new selected employee’s name will override the previous employee’s name as default value.
Read More: How to Reset ComboBox to Default Value Using VBA
Conclusion
So, in this article, I have shown you 3 practical and most frequent uses of VBA ComboBox value in Excel. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative. You are welcome to comment here if you have any further questions or recommendations.
And, visit ExcelDemy to learn about many more Excel problem solutions, tips, and tricks. Thank you!
Related Articles
- How to Use Excel VBA ComboBox with Control Source Property
- Insert ComboBox with Listindex in Excel VBA (with Easy Steps)
- How to Create Cascading Combo Boxes in Excel VBA UserForm
- Create ListBox for Multiple Columns in Excel VBA (2 Easy Ways)
- How to Get Excel VBA Combobox with RowSource Named Range
- Excel VBA to Populate ComboBox from Dynamic Range
- How to Create a Searchable ComboBox with VBA in Excel
