On a worksheet, you can add combo boxes to make data entry easier. These are similar to data validation drop down lists, but have more features and flexibility. For example, they autocomplete, when you start typing in them, and the font size can be changed.
For UserForm combo boxes, see this page.
To add a combo box on an Excel sheet, follow these steps:
You can change the properties, to adjust the font size, number of visible rows, and other settings.
NOTE: Changing the size or location properties of a Combo Box can wipe out Excel's Undo stack, so you won't be able to undo any steps you've previously taken
To see the properties:
To show a list in the combo box, you can use a named range, or the address for a worksheet range. In this example, we'll use a named range -- MonthList.
NOTE: For Excel 2013, see the additional information in the next section.
To add the list:
In Excel 2013, in the ListFillRange property for a worksheet ActiveX combo box, you cannot change the property to use a named range that is based directly on an Excel Table.
In the screen shot below, there is a table named tblProducts. To create a named range with the product names:
If you enter this name in the combo box's ListFillRange property, the name will not be accepted -- the property will be cleared, and the list of names won't appear in the combo box drop down.
To use this list in the combo box, you can create a second name, based on the first name:
Then, in the Combo Box, use the second name -- ProdListB -- in the ListFillRange property, and it will be accepted, because it is not directly based on a table.
When a selection is made in a combo box, you can display the selected item in a cell on the worksheet.
To link the combo box to a specific cell:
To make the list easier to read, to can select a different font and font size.
By default a new combo box shows 8 rows, and you can scroll to see additional rows.
You can change the number of rows, so that more are visible. In this example, the list shows month names, so we can set the number of rows to 12, so all the month names are visible, without scrolling.
To see how the combo box works, you can download the sample file. The zipped file is in xlsx format (Excel 2007 and later), and does not contains macros.
For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.
Search Contextures Sites
Last updated: April 20, 2016 7:52 PM