How to Add ComboBox in Excel (3 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

Excel has a special feature called the ComboBox. We use this feature in Excel when we need a list of options. If you are looking for special tricks to know how to add a combo box in Excel, you’ve come to the right place. There are numerous ways to add a Combobox in Excel. This article will discuss the details of 3 easy methods to add a Combobox in Excel. Let’s follow the complete guide to learn all of this.

Table of Contents hide

What Is Excel ComboBox?

1. Adding Form Control ComboBox in Excel

2. Inserting ActiveX Control ComboBox

3. Adding a Dynamic and Dependent ComboBox Using Excel VBA

Step 1: Insert Userform in VBA Window

Step 2: Create Label and Combobox

Step 3: Add VBA Code

Conclusion

What Is Excel ComboBox?

A combo box is a drop-down list that is based on how a particular text box is combined. The drop-down list allows us to select the option we want. Additionally, we can connect a cell to this list that will display the selected item’s serial number. Excel 2007 to 365 includes the Excel Combo Box feature. There are two types of combo boxes. Those are-

1) Form Controls ComboBox and

2) ActiveX Controls ComboBox.

In the following section, we will use three effective and tricky methods to add combo boxes in Excel. This section provides extensive details on these methods. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.

1. Adding Form Control ComboBox in Excel

Here we are going to demonstrate how to add Form Controls Combobox in Excel. Our dataset contains the names of each day of the week. Here, we’ll add a Combobox that allows you to choose a day from a drop-down menu while also displaying the number of your choice. Additionally, we include a cell that will display the name of the chosen day.

Let’s walk through the following steps to add a form control Combobox in Excel.

📌 Steps:

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

Next, place the combo in the desired location on your worksheet.

Then, press the right button of the mouse and choose the Format Control option from the Context Menu.

Therefore, the Format Objects window will appear.
Then, select the Control tab.
Here, we choose the range that includes the drop-down values for the input range. The cell that will display the serial number of the choice is the one that the Cell link box alludes to. The number of options in the drop-down was indicated by the drop-down’s lines.
Finally, click on OK.

Now, click on the down arrow of the drop-down.

Hence, a list of options will be shown here. Choose an option from the drop-down list.
We can see that 2 is shown in 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.
Then, write down the following formula in it.

=INDEX(B5:B11,D5)

Next, press Enter.
Therefore, you will get the following output.

2. Inserting ActiveX Control ComboBox

We will demonstrate how to create an ActiveX Controls combo box in this section. The VBA code in this combo box has an additional facility that we can use. We will just show the result using the combo box in this section at cell D5. Here are the steps to add the ActiveX Control combo box.

📌 Steps:

First, to create a Name, you have to click on the Formulas tab. Then, select the Define Name option.

Therefore, the New Name window will appear.
Next, input the name of the range in the Name box.
Then, choose the range at Refers to the box from the worksheet.
Finally, click on OK.

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

Next, place that combo box beside cell D5.

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

Consequently, the Properties window appears.
Find out the LinkedCell and ListFillRange options from the Properties window.
Next, insert D5 as the 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.
Consequently, a list is shown here.
Choose any of the options.
Therefore, you will get the following output in cell D5.

Similar Readings

3. Adding a Dynamic and Dependent ComboBox Using Excel VBA

With the help of a VBA macro in Excel, we will create a dynamic and dependent ActiveX Controls combo box. There are two columns here: Days and Months. Here, we’ll present two combo boxes. 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. You have to follow the following steps to complete the task.

Step 1: Insert Userform in VBA Window

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

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

Therefore, we can see a UserForm appears with a Toolbox.

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

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

Step 2: Create Label and Combobox

Now, add a Label and ComboBox from the Toolbox.

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

Afterward, 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.
Next, press the run option from the main tab.

This is the output.

Step 3: Add VBA Code

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

A code will be added to the window to activate the Userform.

Then, remove the Userform code from the VBA window.
Now, write down the following code in 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.
Double-click on the ComboBox1.

Afterward, 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.

Therefore, 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.

Finally, go to the UserForm.
Consequently, we can see the new column is added in the combo box.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the step-by-step process.

Add ComboBox.xlsm

Conclusion

That’s the end of today’s session. I firmly believe that from now on, you may be able to add a Combobox in Excel. If you have any queries or recommendations, please share them in the comments section below.

Don’t forget to check our website ExcelDemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!

How to Add ComboBox in Excel (3 Easy Methods)

What Is Excel ComboBox?

1. Adding Form Control ComboBox in Excel

2. Inserting ActiveX Control ComboBox

3. Adding a Dynamic and Dependent ComboBox Using Excel VBA

Step 1: Insert Userform in VBA Window

Step 2: Create Label and Combobox

Step 3: Add VBA Code

Conclusion

Related Articles

Leave a reply Cancel reply