How to Use Excel Combo Box (A Complete Guideline)

Do you want to learn how to use Excel Combo box? Combo box in Excel provides predefined options, assuring precise inputs and allowing efficient data sorting and filtering. When we need to use a list of options in Excel we use this feature.

In this article, we will discuss the Excel Combo box in detail with the proper illustration. We have demonstrated how to create Form Control and ActiveX Control combo boxes in Excel. Also we have illustrated the method of making a dynamic and dependent combo box.

After adding a combo box, you may want to remove it. So we have provided the procedure for that as well.

While creating this article, we have utilized Excel 365. But the methods in this article are also applicable in other Excel versions.

What Is Excel Combo Box?

Combo Box is a drop-down list based on the combination of a certain text box. From the drop-down list, we can choose our desired option. Also, we can link a cell with this list that will show the serial number of the chosen item. Excel Combo Box is available in from Excel 2007 to 365.

How to Add a Combo Box in Excel 2007/2010/2013/2016 or in More Updated Versions

In this section, we will show the basic procedure of how to add a combo box in Excel. The following procedure will apply to all Excel versions of the combo box.

To add the combo box, we need to enter the Developer tab. Usually, the Developer tool is not available in the Excel Ribbon options.

📌 Steps:

Go to File >> Options. The Excel Options window appears here.
Choose Customize Ribbon option from the left side.
Then, go to the Main Tabs from the Customize the Ribbon column.
Find out the Developer option from the list.
Check the corresponding box of the Developer option.
Finally, press OK.

Return to the sheet.

We can see the Developer tab is available now.

Click on the Developer tab.
Choose the Insert option from the Controls group.

Options of the Insert tab are shown here. This window indicates two combo boxes of two different types.

Now, select any of the marked combo boxes.
Then place the cursor in the sheet of the desired location.

We can also customize the size of the combo box.

How to Add 2 Types of Excel Combo Box

We already know that there are two types of combo boxes. Those are-

Form Controls Combo Box and
ActiveX Controls Combo Box.

In the below section, we will discuss those two combo boxes.

How to Add Form Control Combo Box

In this section, we will show how to add Form Controls Combo Box in Excel.

We have a dataset of the name of days of the week. Here, we will add a Combo Box that will select a day from the drop-down list and show the selection number. Also, we add a cell that will show the name of the selected day.

📌 Steps:

First, select the combo box from the Form Controls section.

Place the combo box in the desired location on the sheet.

Press the right button of the mouse.
Choose the Format Control option from the Context Menu.

The Format Objects window appears.
Choose the Control tab now.

The input of the values on the appeared boxes. In the Input range, we select the range that contains values of the drop-down.

The Cell link box refers to a cell that will show the serial number of the selection.

The Drop down lines indicated how many options will appear in the drop-down.

Finally, press OK.
Now, click on the down arrow of the drop-down.

A list of options is shown here.

Choose an option from the drop-down list.

We can see that 2 is shown on cell D5. This cell is linked with the drop-down list.

Now, we additionally want to show the value of the selection or the name of the selected day in a specific cell.
We apply a formula for that.
Put the following formula on cell E5.

=INDEX(B5:B11,D5)

Press the Enter button to get the result.

So, the full process of the Form Controls combo box is shown here.

How to Create ActiveX Control Combo Box

In this, section, we will show how to create an ActiveX Controls combo box. There is an additional facility where we can use the VBA code in this combo box.

We will just show the result using the combo box in this section at cell D5.

📌 Steps:

First, we need to create a Named Click on the Formulas tab. Then, select the Define Name option.

The New Name window will appear.
Input the name of the range in the Name box.
Then, choose the range at Refers to box from the Excel Sheet. Finally, press OK.

Now, insert a combo box from the ActiveX Controls section.

Place that combo box beside cell D5.

Now, press the right button of the mouse.
Choose the Properties option from the Context Menu.

The Properties window appears.
Find out the LinkedCell and ListFillRange options from the Properties window.

Insert D5 as linked cell and Day as the listed range.

Now, disable the Design Mode from the Controls group.
After that, click on the down arrow of the combo box.

A list is shown here.
Choose any of the options.

We can see that day is showing at cell D5.

Similar Readings

Excel VBA to Make a Dynamic and Dependent Combo Box

Now, we want to make a dynamic and dependent ActiveX Controls combo box using VBA macro in Excel.

Here, we have two columns: Days and Months. We will introduce two combo boxes here. The second combo box will be dependent on the 1st combo box. Firstly, we will select the category in the 1st combo box and from the 2nd box, we will get the options under that box.

📌 Steps:

First, go to the Developer tab.
Click on the Visual Basic option from the Code group.

Then, the VBA window will appear.
To make a dynamic and dependent combo box we will need a UserForm.
Choose the UserForm option from the Insert tab.

We can see a UserForm appears with a Toolbox.

Now, press the right button of the mouse keeping the cursor on the UserForm.
Choose the Properties option from the Context Menu.

From the Properties window go to the Caption Put a name here. This is the title of the UserForm.

Then add a Lable and ComboBox from the Toolbox.

Now, copy those boxes by Ctrl+C and paste them by pressing Ctrl+V.

Now, move the cursor on any of the Labels and press the right button.
Choose the Properties option from the Context Menu.

Now, change the name, font color, size, and others from this Properties window.

After changing the attributes our Userform will look like this.
Now, press the run option from the main tab.

This is the look.

Now, double click the UserForm, and we enter the VBA window to where will write our code.
In the window, go to the right side and click on the arrow.
We will choose Activate to option the list.

A code will add to the window to activate the UserForm.

Remove the code of UserForm code from the VBA window.
Now, copy and paste another VBA code into the window.

Private Sub UserForm_Activate()
Dim D_Sheet As Worksheet
Set D_Sheet = ThisWorkbook.Sheets("Dependent & Dynamic Combo Box")
Dim N As Integer
Me.ComboBox1.Clear
For N = 1 To Application.WorksheetFunction.CountA(D_Sheet.Range("1:1"))
    Me.ComboBox1.AddItem D_Sheet.Cells(1, N).Value
Next N
End Sub

When we click on the down arrow of the Category combo box and see the options.
Again, click on the down arrow of the Options combo box.

The Options combo box is empty, but the Category combo box is not empty.

Again, double-click on the ComboBox1.

Copy another VBA code and paste it into the window.

Private Sub ComboBox1_Change()
Dim D_Sheet As Worksheet
Set D_Sheet = ThisWorkbook.Sheets("Dependent & Dynamic Combo Box")
Dim N, M As Integer
M = Application.WorksheetFunction.Match(Me.ComboBox1.Value, D_Sheet.Range("1:1"), 0)
Me.ComboBox2.Clear
For N = 2 To Application.WorksheetFunction.CountA(D_Sheet.Cells(1, M).EntireColumn)
    Me.ComboBox2.AddItem D_Sheet.Cells(N, M).Value
Next N
End Sub

Again, run the VBA code by pressing the F5 button.

We can see the Options combo box is working now. It means the Options combo box is dependent.

Now, we want to make the combo box dynamic.
We add another column to the dataset.

Again, go to the UserForm.

And we can see the new column is added in the combo box.

How to Remove Combo Box in Excel

In this section, we will show how to remove the combo box.

📌 Steps:

First, we will click on the Developer tab.
Enable the Design Mode.

Select the combo box.

Now, press the delete button from the keyboard.

We can see the combo box has already been deleted from the sheet.

Excel VBA ComboBox: Knowledge Hub

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Using Combo Box.xlsm

Conclusion

In this article, we described all about the Combo box. How to insert, make it dynamic, and delete it from the Excel sheet. I hope this will satisfy your needs. Please have a look at our website ExcelDemy and give your suggestions in the comment box.

Get FREE Advanced Excel Exercises with Solutions!

How to Use Excel Combo Box (A Complete Guideline)

What Is Excel Combo Box?

How to Add a Combo Box in Excel 2007/2010/2013/2016 or in More Updated Versions

How to Add 2 Types of Excel Combo Box

How to Add Form Control Combo Box

How to Create ActiveX Control Combo Box

Excel VBA to Make a Dynamic and Dependent Combo Box

How to Remove Combo Box in Excel

Excel VBA ComboBox: Knowledge Hub

Conclusion

Related Articles

Leave a reply Cancel reply