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.
- 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.
Procedure to Add Item to ComboBox with VBA in Excel
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.
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
- Now, Run the code and click the drop-down of the ComboBox. You will find the items that have been added to the ComboBox.
- With the same procedure, you can create another ComboBox based on your data.
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.
- 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.
Sub Clearing_ComboBox() With Sheet1 .rfComboBox.Clear End With End Sub
- 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.
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.
- 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.
- Hence, the ComboBox will be removed from the worksheet.
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.
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.
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!