We use the Combo box, which is a drop-down list element to gain more control over the input of our user. In this article, we will explore two easy ways to create a combobox in excel. One of these options is called ActiveX Control while the other is Form Control.
Figure 1 – How to use combobox
Setting Up a Workbook to Create Combobox
If we cannot find the Developer tab in the ribbon at the top of our spreadsheet, here is what we will do.
- We will go to the File tab and select Options
Figure 2 – Combo box
- In the Excel Options dialog box, we will click on Customize Ribbon.
- In the column on the right, we will mark the checkbox for the Developer
Figure 3 – Combo box values
- If we can’t find the Developer in that column, we will go to Choose Commands from and select All Tabs. Next, we will click on Developer to highlight and lastly tap the Add button.
- We will go back to the columns under Customize ribbons and mark the checkbox for Developer.
Figure 4 – How to use combo box
How to Create a Form Control Combo box
- We will go to the Developer tab and click the arrow under Insert in the Controls group
Figure 5 – Combo box
- Next, we will select Form Controls and click on Combo box
Figure 6 – Combo box
- Once we click on the Combo box, we can now drag our mouse and draw the combo box in our worksheet.
Figure 7 – Combo box values
- Next, we will right-click on the new combo box element and select Format Control
Figure 8 – Form control combo box
- In the Format Object dialog box, we will go to the Control tab. We will enter the range of our cells, change the drop lines to 8 and select OK
Figure 9 – How to use combo box
- We will now be able to click on the down arrow in the combobox to see our list.
Figure 10 – How to add combo box
How to create an ActiveX Control Combo box
An ActiveX Control Combo box is more flexible because we can make changes to its font, size, and colors. Here’s how we can create one.
- First, we will create a named range. We will go to the Formulas Tab, then Defined Names and select Name Manager
Figure 11 – Creating combo box based on a named range
- In the Name Manager dialog box, we will select New and name the range camping sales with no space between words.
- We will click on the Refers to the box and use our mouse to click the range from B3 to B8 that includes our Camping sales list
Figure 12 – Excel ActiveX combo box
- We will click OK to see the new range in the Name Manager list
Figure 13 – Userform combobox
- Next, we will place our cursor where we want the combo box and drag to create
Figure 14 – Combo box in Excel 2013
- We will right-click on it and choose Properties
Figure 15 – How to add combobox
- In the properties dialog, we will find ListFillRange
Figure 16 – Combo box
- Next, we will type named range, Campingsales
- We will close the dialog box.
- We will go to the Developer tab and select the Design mode in the Controls group.
- Now, we can check the combobox to see our list of values
Figure 17 – Using Excel ActiveX combo box.