Show Data Validation Items in List Box

In February, I shared a sample file that shows a popup list box, when you click on a cell that has a data validation list. The list box shows all the items from the source list, with check boxes, so you can select multiple items.

When you click OK, all the selected items are added to the cell, separated by a comma and space.

Listbox For Single Selections

Multiple selection is useful sometimes, but in most worksheets, you probably want people to select just one item in a data validation cell. So, I’ve created a new version of the List Box sample file, with a single-selection List Box.

• Click a data validation cell, to see the List Box.
• Then, click an item to select it, and click OK, to enter that item in the active cell

List Box Pros and Cons

There are some pros and cons to using a List Box, instead of the built-in data validation:

Pros:

• The list length is adjustable – see more than the default 8 items
• Font size can be increased, for easier reading
• List can be wider – great for narrow columns, where it’s hard to see the longer data validation items

Cons:

• Requires macros, which must be enabled
• Wipes out the Undo list, if you select an item (like all macros that change the worksheet)

Download the Sample File

To see the code, and experiment with the Listbox, you can download the single-selection sample file from my Contextures website.

The zipped file is in xlsm format, so enable macros when you open the file.
__________________________