When working with Excel, ComboBox is a great tool to select and process data. You can easily select values from given ComboBox values. Now, there 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.
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 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.
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 input 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 to 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.
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.
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 newly selected employee’s name will override the previous employee’s name as the default value.
Download Practice Workbook
You can download our practice workbook from here for free!
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. Thank you!
Related Articles
- How to Use VBA to Populate ComboBox List from Range in Excel
- 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
- Create ComboBox with RowSource in Excel VBA
- How to Get Excel VBA Combobox with RowSource Named Range
