Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Add ComboBox in Excel (3 Easy Methods)

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.


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.


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.


3 Easy Methods to Add ComboBox in Excel

In the following section, we will use three effective and tricky methods to add combo box 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. Add 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.

Add Form Control ComboBox

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.

click on insert from developer tab to add combobox in excel

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

add and place Form Control Combo Box

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

click on Format Control

  • 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.

show the output to demonstrate how to Add Form Control ComboBox

  • Hence, a list of options will show 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.

apply index formula

Read More: How to Use VBA to Populate ComboBox List from Range in Excel


2. Insert 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.

Add ActiveX Control Combo Box

📌 Steps:

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

define name to add Combobox in Excel

  • 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.

Insert ActiveX Control ComboBox

  • 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.

select properties to demonstrate how to add ActiveX Control ComboBox

  • 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.

show the output

Read More: How to Use ListFillRange Property of ComboBox in Excel


3. Add 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.

dataset to Add a Dynamic and Dependent Combo Box Using Excel VBA


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.

select userform to Add a Dynamic and Dependent ComboBox Using Excel VBA

  • 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.

select properties 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.

Add a lebel and Dependent ComboBox Using Excel VBA

  • 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.

change the font color and other properties

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

final output to demonstrate how to Add a Dynamic and Dependent ComboBox Using Excel VBA

  • 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.

click on Activate option

  • A code will add 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.

show the output

  • The Options combo box is empty, but the Category combo box is not empty.
  • Double-click on the ComboBox1.

Add a Dynamic and Dependent ComboBox

  • 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.

insert new column in dataset to demonstrate how to Add a Dynamic and Dependent ComboBox

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

Read More: How to Create a Searchable ComboBox with VBA in Excel


Conclusion

That’s the end of today’s session. I firmly believe that from now, 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!


Related Articles

Saquib

Saquib

Welcome to my Profile. I am working on and researching Microsoft Excel right now, and I will be posting articles about it here. I received a B.Sc. in Naval Architecture and Marine Engineering from the Bangladesh University of Engineering and Technology (BUET). Having studied naval architecture, I have a strong interest in research and development. Always try to learn from different sources and come up with creative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo