Combo Box in Excel (Table of Contents)
Combo Box in Excel
Combo Boxes in excel is not used for VBA Code but also for Excel spreadsheets. Combo boxes are available under the Insert option of Developer menu tab. These boxes are drop-down kind of elements which allow the user to select any value from the values listed in Combo Box drop-down list. To select the Combo box option from Insert drop down and drop it anywhere in sheet. And to add the values into the list click right and select Format Control, select the input range and cell link.
There are two options for creating a “Combo Box” in Excel:
- Form Controls “Combo Box”:- It comes inbuilt with excel. In this type of control box, users cannot make changes to properties.
- ActiveX Controls “Combo Box”: – It is more versatile than form control. In this type of control box, a user can make changes to properties.
2. An example used for the two methods
In order to distinguish between the two combo boxes, let us look at the following examples –
How to Create Combo Box in Excel?
Initial steps before creating a Combo Box in Excel –
In case, the Developer tab is visible beside the View tab in the Excel Ribbon then,
- Click on Developer-> Insert-> select “Combo Box” from either Form Controls or ActiveX Controls options.
In case, the Developer tab is not visible beside the View tab in the Excel Ribbon then,
- Click on File -> Options.
- “Excel Options” dialogue box will be displayed as shown below. Click “Customize Ribbon”. On the right panel, under “Customize the Ribbon” drop-down box, select “Main Tabs” value, selected by default. In the “Main Tabs” panel, select the Developer check box. Click Ok.
- You should now find the Developer tab in your Excel Ribbon.
- Create a simple list of values in the worksheet to start with, irrespective of the type of “Control Box”.
Form Controls “Combo Box” in Excel
Steps to create Form Controls “Combo Box” in Excel
- Go to the Developer tab in the Excel Ribbon -> click Insert -> click on “Combo Box” under “Form Controls”.
- Click on the Excel Sheet at a location where you want to place the “Combo Box”. You may select the “Combo Box” and drag any of the small squares in the middle of the boundaries or circles in the corners to change the size of the “Combo Box”.
- Right, click on the new “Combo Box” -> select “Format Control”. The “Format Control” dialog box will appear.
- Select Control
- Click on the button to the right of the “Input range”.
- Select the cell range for the values in the list which auto-populates the input box. Click on the highlighted button.
- Click OK in the “Format Object” dialog box.
Note: Drop down lines =8(by default), can be configured to show a number of items in the “Combo Box”.
- It Shows 8 number of items in the dropdown list.
Cell link – Enter cell number to display position of a selected value in the list.
- It shows the position of months.
- Now you will be able to click on the drop-down arrow of the new “Combo Box” to see the list.
Steps to delete Form Controls “Combo Box” in Excel
- Go to Developer tab -> “Design Mode”
- Select the Form Controls “Combo Box” and press Delete.
Point to be remembered at the time of creating Form Control Combo Box
- It helps the user to choose a value from the range of items.
- In Form Control Combo Box, a user cannot make changes in the properties.
ActiveX Control Combo Box in Excel
The procedure to create ActiveX Control “Combo box” is a bit different from that of Form Control “Combo Box”.
Steps to create ActiveX Control Combo Box in Excel
- Follow either of the 2 ways to view “New Name” dialog box –
- Go to Formula tab -> click on “Name Manager” -> Click New in the “Name Manager” dialog box.
OR
- Go to Formula tab -> click “Define Name”.
- Enter Name. Months. Click on the button to the right of the “Refer to” input box.
- Select the cell range which auto-populates “Refers to” input box. Click on the highlighted button.
- Click OK in the restored “New Name” dialogue box.
- You will find a new range by the name, ”Months” in the “Name Manager” list.
- To create the “Combo Box”, we need to select “Combo Box” under “ActiveX Controls”.
- Click on the Excel Sheet at a location where you want the “Combo Box” to be placed. Right, click on the “Combo Box” -> click on Properties.
- A properties dialog box will appear.
- In the Properties dialog box, in ListFillRange field, type the name of named range e.g. Months, that we have created above. Click on X in the upper right corner of the dialog box to close the same.
- Save the file with “.xlsm” extension and reopen the file.
- Click on a pointer in “Combo Box” to see the list of values.
- To edit various properties e.g. font size, color etc. make changes through a Properties dialog box.
To select/deselect “Combo Box”, click on “Design mode” in the controls group of the Developer.
- To deselect “Combo Box”, deselect “Design Mode”.
- To select “Combo Box”, select “Design Mode”.
- Select “Combo Box” as shown in the following.
Steps to delete Active X Control Combo Box in Excel
- Go to the Developer tab, turn on “Design mode”.
- Select the Active X control combo box that you want to delete and Press Delete.
Things to Remember About Combo Box in Excel
- It is more versatile.
- It allows the user to type the value in the text box from the range of items.
- In ActiveX Control “Combo Box”, the user can edit properties e.g. font size, colors, etc.
- It is commonly used as a user interface control from where a user can select an item as per own choice.
- It makes data entry easier and have many features and flexibility.
- Two methods of creating Combo boxes are available in MS Excel, giving more options to do the work in an easier way.
Recommended Articles
This is a guide to Combo Box in Excel. Here we discuss its uses and how to create Combo Box in Excel with excel examples and downloadable excel templates. You may also look at these useful functions in excel –
