Rafiul Hasan
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.
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.
- 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.
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
- 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.
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
- 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.
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.
- Hence, the ComboBox will be removed from the worksheet.
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!
