How to Add Item to ComboBox with VBA in Excel

Get FREE Advanced Excel Exercises with Solutions!

You might be roaming around how you can clear items from VBA ComboBox in Excel. No need to worry anymore! You have come to the right place. Today we are going to learn how to add ComboBox, add items to ComboBox, and also clear items from the ComboBox with VBA in Excel.


What Is ComboBox in Excel?

ComboBox is a unique feature of Excel for creating a list of options. So, basically, it is one type of drop-down list on the basis of the combination of a specific text box. The list created here can also be linked to another cell for showing the position of a particular chosen item.

There are two types of ComboBox available in Excel.

  • Form Controls ComboBox
  • ActiveX Controls ComboBox

How to Add ComboBox in Excel

Let’s say, we have a dataset of some customers visiting a restaurant and their ordered food items.

We want to show the process of adding ComboBox in Excel based on the dataset. Now, we want to create ComboBox from Customer or their Ordered Food Items. We will show the process for ActiveX Controls. Let’s review the process briefly.

Steps:

  • First of all, go to the Developer tab on the Excel Ribbon.
  • Then select the Insert menu under the Controls group.
  • Here, you will find two distinct sections: Form Controls & ActiveX Controls. Select the ActiveX Controls and Click the ComboBox icon of the ActiveX Controls group.

  • Now, your cursor will change its shape which means you are ready for inserting ComboBox. Now drag the cursor over the area of the Excel worksheet and you will see the ComboBox has been inserted into the home window. In the Formula bar, you will see the text “=EMBED(“Forms.ComboBox.1″,””)” which indicated the existence of ComboBox.

Here, after adding ComboBox, you can change its property. For this,

  • Click on the ComboBox -> go to the Developer tab-> click Properties.
  • As a result, a list of properties will appear in the window.
  • Here, we have changed the customized (Name) to rfComboBox.

Read More: Excel VBA ComboBox: Important Properties to Explore


Procedure to Add Item to ComboBox with VBA in Excel

Now, let’s Add items to the ComboBox you have inserted just now.

Steps:

  • Here, press ALT+F11 to open the Visual Basic Editor window.
  • Then, open the Sheet Code window and apply the following code. Add items as per your requirement. Or you can just double-click the ComboBox to activate the sheet code window.

Code:

Private Sub Adding_items_ComboBox()
With Sheet1.rfComboBox

    .AddItem "Mike"
    .AddItem "Adam"
    .AddItem "Steve"
    .AddItem "Stuart"
    .AddItem "Hopper"
    .AddItem "Milford"
    .AddItem "David"

End With
End Sub

ComboBox Add Item VBA

  • Now, Run the code and click the drop-down of the ComboBox. You will find the items that have been added to the ComboBox.

ComboBox Add Item VBA

  • With the same procedure, you can create another ComboBox based on your data.

ComboBox Add Item VBA


How to Clear Item from ComboBox with VBA in Excel

Now you know how to add items to ComboBox with VBA. Let’s learn how to clear items from ComboBox using VBA in Excel.

Let’s proceed with the following steps for serving that purpose.

Steps:

  • First of all, double-click on the comboBox to activate the sheet code window.
  • Then, apply the VBA code in the window for clearing items. You can use the following.

Code:

Sub Clearing_ComboBox()

    With Sheet1
        .rfComboBox.Clear
    End With

End Sub

VBA Combobox Clear Items

  • Here, just Run the code and as a result, you will see that the VBA code has cleared items from the ComboBox and made the drop-down list empty.

VBA Combobox Clear Items

Every time when you need to clear items from the ComboBox, just apply the code and your task will be done.


How to Remove ComboBox from Excel

There might be situations when you won’t need your ComboBox anymore. In that case, you will want to remove the ComboBox from your worksheet.

Steps:

  • Just go to the Developer tab and activate the Design Mode by clicking on it a single time.
  • Then, select the ComboBox and press the DELETE key on the keyboard.

Remove VBA ComboBox and Clear Items

  • Hence, the ComboBox will be removed from the worksheet.

Remove VBA Combobox and Clear Items


Practice Section

Here I am providing you with a practice section so that you can try it yourself. I have inserted a ComboBox. Try to apply the VBA code and add item to the box.

ComboBox Add Item VBA


Things to Remember

  • Every time you need to add and clear items from the ComboBox, run the corresponding code.
  • If you customize the name of the ComboBox from the property, don’t forget to change it in the VBA code.

📁 Download Practice Workbook

You can download the practice book from the link below.


Conclusion

In this tutorial, I have tried to show you the procedures to add and clear items from your ComboBox with VBA in Excel. I hope this article has shed some light on your way. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box. For more queries, kindly visit our website. Keep in touch for more related articles!


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Rafiul Hasan
Rafiul Hasan

Rafiul Hasan, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering & Technology, contributes significantly to the ExcelDemy project with almost 1.6 years of dedicated work. Currently an Excel and VBA Content Developer, he has a passion for problem-solving. Authoring over 100 articles for ExcelDemy showcases expertise in Microsoft Office Suites and Data Analysis. In addition to content development, Rafiul actively engages with the ExcelDemy forum, offering valuable solutions to user queries and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo