What Is a ComboBox in Excel?
A ComboBox allows users to create or choose from a list of options. It is a type of a drop-down list based on a combination of specific text boxes.
There are two types of ComboBox available in Excel.
- Form Controls ComboBox
- ActiveX Controls ComboBox
How to Add a ComboBox in Excel
We have a dataset of some customers visiting a restaurant and their ordered food items. We want to create a ComboBox from Customer or their Ordered Food Items.
Steps:
- Go to the Developer tab on the Excel Ribbon.
- Select the Insert menu under the Controls group.
- You will find two distinct sections: Form Controls & ActiveX Controls. Select the ActiveX Controls and click the ComboBox icon of the ActiveX Controls group.
- Your cursor will change to a plus.
- Click and drag the cursor over the area of the Excel worksheet and you will see the ComboBox has been inserted.
- In the Formula bar, you will see the text “=EMBED(“Forms.ComboBox.1″,””)” which indicates the existence of a ComboBox.
After adding a ComboBox, you can change its properties.
- Click on the ComboBox, then go to the Developer tab and click Properties.
- A list of properties will appear in the window.
- We have changed the customized (Name) to rfComboBox.
Read More: Excel VBA ComboBox: Important Properties to Explore
Procedure to Add an Item to a ComboBox with VBA in Excel
Steps:
- Press Alt + F11 to open the Visual Basic Editor window.
- Open the Sheet Code window and apply the following code. Add items as per your requirement. You can also 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
- Run the code and click the drop-down of the ComboBox. You will find the items that have been added to the ComboBox.
- You can create another ComboBox based on your data.
How to Clear Items from a ComboBox with VBA in Excel
Steps:
- Double-click on the ComboBox to activate the sheet code window.
- Apply the VBA code in the window for clearing items:
Code:
Sub Clearing_ComboBox()
With Sheet1
.rfComboBox.Clear
End With
End Sub
- Run the code and you will see that the VBA code has cleared items from the ComboBox and the drop-down list empty.
How to Remove a ComboBox from Excel
⏩ Steps:
- Go to the Developer tab and activate the Design Mode by clicking on it a single time.
- Select the ComboBox and press the Delete key on the keyboard.
- The ComboBox will be removed from the worksheet.
Practice Section
We’re providing you with a practice section so that you can try it yourself. We have inserted a ComboBox. Try to apply the VBA code and add items 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 the Practice Workbook
Related Articles
- Excel VBA ComboBox Value
- How to Use VBA to Populate ComboBox List from Range in Excel
- How to Use Excel VBA ComboBox with Control Source Property
- Excel VBA to Populate ComboBox from Dynamic Range
- Create ComboBox with RowSource in Excel VBA
- How to Get Excel VBA Combobox with RowSource Named Range
