Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Excel VBA ComboBox Value (Add, Remove or Set Default Value)

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.

Sample Dataset to Add VBA ComboBox Value

📌 Steps:

  • First, go to the Developer tab >> Insert tool >> Combo Box (ActiveX Control option).

Insert a VBA ComboBox to Work with Value

  • 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.

ComboBox in Excel

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.

Read More: How to Create a Searchable ComboBox with VBA in Excel


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.

Double Click on ComboBox to Add Value with VBA

  • 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.

VBA ComboBox Value Code

  • 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.

Run VBA to Add ComboBox Value

Thus, you will see the values will be added into the combo box. And, the final outcome should look like this.

ComboBox Output

Read More: How to Select First Item from ComboBox Using 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.

Access Properties Tool

  • As a result, the Properties window will appear.
  • Here, input the range B5:B9 in the ListFillRange option’s text box.

Properties Window

As a result, the values of B5:B9 cells will be input into the dropdown and the final result would look like this.

ComboBox Dropdown

Read More: Excel VBA ComboBox: Important Properties to Explore


Similar Readings


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.

Run VBA to Remove ComboBox Value

Consequently, you will see that all the values are removed from the dropdown and the result should look like this.

Removed ComboBox Values

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.

Access View Code Tool

  • 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.

Run VBA to Set Default ComboBox Value

  • 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.

Default ComboBox Value

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

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo