How to Use Excel Combo Box (A Complete Guideline)

The Combo box is a special feature of Excel. 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.


Download Practice Workbook

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


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.

Enable developer mode in Excel

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

Multiple combo box options of Excel

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.

Place an Excel combo box

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.

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

Add Form Control Combo Box in Excel

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

Customize the combo box

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.

Select option from the combo box drop-dwon

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.


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

Insert a Named Range in Excel

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

Customize a Named Range

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

Create ActiveX Control Combo Box in Excel

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

Link the Combo box with Cell and Named Range

Insert D5 as linked cel 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.


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.

Enter to Excel VBA

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

Create a UserForm in Excel

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

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

Customize the Labels of the Excel

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

Final look of the Combo box

  • 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

Excel VBA to Make a Dynamic and Dependent Combo Box

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

Use of the combo box in Excel

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.

Add new column in 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.

Enable the Design Mode

  • Select the combo box.

How to Remove Combo Box in Excel

  • Now, press the delete button from the keyboard.

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


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.com and give your suggestions in the comment box.


Similar Readings


Related Articles

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

3 Comments
  1. Hallo Kawser,
    have ever tried to use ActiveX Controls within VSTO, I have troubles addressing their event handling procedures.
    stefan

  2. Reply
    Krishnaprasad Menon Nov 7, 2016 at 10:56 AM

    Excellent tutorial. Thanks

Leave a reply

ExcelDemy
Logo