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

How to Clear Items from VBA ComboBox in Excel

Previously we have discussed the process of how to use ComboBox in Excel. You might be roaming around how you can clear items from VBA ComboBox in Excel. No need to worry anymore! Today we are going to learn how to clear items with VBA ComboBox in Excel.


📁 Download Practice Workbook

You can download the practice book from the link below.


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

Add ComboBox in Excel

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

Dataset for ComboBox to Clear Items with VBA in Excel

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.


Step 1: Insert ComboBox

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

Add ComboBox in Excel

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

Change Property for ComboBox in Excel


Step 2: Add Items to ComboBox

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

  • 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 rfComboBox_Change()
With Sheet1.rfComboBox

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

End With
End Sub

VBA ComboBox Add Items

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

VBA ComboBox Add Items

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

VBA Combobox Add Items

Read More: Insert ComboBox with Listindex in Excel VBA (with Easy Steps)


Clear Items with VBA from ComboBox 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.

Read More: How to Use ComboBox to Get Selected Item Utilizing VBA


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

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


Practice Section

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


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.

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

Rafi

Rafi

Hey there! I am Md. Rafiul Hasan. Currently I am working as an Excel & VBA content developer. I like new ideas and want to explore the field of innovation. Excel has saved our worktime and made it easy for us to quick calculations. I am trying to make it easier for you to overcome the obstacles you face while working on Excel. Stay connected!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo