Table of Contents
- Combo Box in Excel
- How to Create Combo Box in Excel?
- Form Controls “Combo Box” in Excel
- ActiveX Control Combo Box in Excel
- Steps to delete Active X Control Combo Box in Excel
Combo Box in Excel
Combo Boxes in excel is not used for VBA Code but also for Excel spreadsheets. It is available under the Insert option of the Developer menu tab. These boxes are drop-down kind of elements that allow the user to select any value from the values listed in the Combo Box drop-down list. To select the Combo box option from Insert drop down and drop it anywhere in the 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.
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”. Under the “Customize the Ribbon” drop-down box, select the “Main Tabs” value, selected by default, on the right panel. 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:
- 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 “Combo Box” size.
- 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 the 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
- 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 the “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 the “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 the ListFillRange field, type the name of the 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 the “.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
- 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 where users can select an item as per their own choice.
- It makes data entry easier and has many features and flexibility.
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 –
