Contextures

Data Validation Combo Box Resources

Overcome the limitations of a drop down list by using a combo box, that refers to named ranges which contain the list items. Use these data validation combo box resources to build one in your Excel file.

Introduction

With Excel's Data Validation, you can add drop down lists in worksheet cells. Those lists have limitations though:

  • list font can't be changed
  • number of visible rows can't be changed (maximium of 8)
  • no AutoComplete feature as you start to type.

standard data validation

With a Data Validation Combo Box, you can overcome those limitations, and make it easier to enter data.

The following resources will help you get started.

Combo Box Tutorials

Before you add the combo box, the cells need to have data validation drop down lists set up. See the instructions on these pages:

Data Validation Basics

Create Dependent Drop Down Lists

For data validation combo box tutorials, check the following pages:

Data Validation With Combo Box

Data Validation Combo - Lists on Other Sheet

Data Validation Combo - Click

NOTE: You can't use ActiveX controls on a Macintosh. Prof. Lee Townsend shows how to create drop downs using Form control combo boxes.

Video: Data Validation Combo Box

To see how the combo box works, and appears when you double-click a data validation cell, watch this short video.

Download the Sample File

These are the data validation combo box sample files from the tutorial pages. Instructions are here

Basic: Double-click a cell that contains a data validation list, and a combobox appears -- font size can be set, more than 8 rows displayed, autocomplete can be enabled. Lists are stored in named ranges on a separate sheet. Macros must be enabled. To test this combo box code, download the zipped sample file.

Dependent: For the Dependent Combo sample, click here to download.

Dependent Dynamic: This sample file shows a combo box for dependent drop downs based on a dynamic named range.

Other Combo Box Examples

DV0067 - Select Multiple Items in ComboBox or ListBox
Click a cell that contains a data validation list, and a form appears. Click items in the ListBox, or type in the ComboBox and click Add. Then, click OK, to add all selected items to the cell. Lookup lists are in named ranges on different sheet. Click here to download datavallistboxcombo.zip

DV0065 - Data Validation Click Combobox - Named Range
Click a cell that contains a data validation list, and a combobox appears -- font size can be set, more than 8 rows displayed, autocomplete can be enabled. Lookup lists are in named ranges on different sheet.Click here to downloaddatavalcomboboxsheetclick.zip

DV0062 - Data Validation Click Combobox - Add New Items -- Click a cell that contains a data validation list, and a combobox appears -- font size can be set, more than 8 rows displayed, autocomplete can be enabled. If a new item is entered, you will be asked if you want to add it to the existing list. Click here to download: DataValCombobox_AddSort_Multi.zip

DV0057 - Data Validation Combobox Codes -- Double-click a cell that contains a data validation list, and a combobox appears, showing a list of descriptions. Select a descriptions, and that descriptions numeric code is entered in the cell. Lists are stored in named ranges on a separate sheet. Click here to download: DataValComboboxCodes.zip

DV0055 - Dependent Data Validation Click Combobox -- Click a cell that contains a data validation list, and a combobox appears -- font size can be set, more than 8 rows displayed, autocomplete can be enabled. List in second column is dependent on selection in first column. Click here to download: DataValComboClickDepend.zip

DV0043 - Data Validation Combobox With Entry Check -- Double-click a cell that contains a data validation list, and a combobox appears -- font size can be set, more than 8 rows displayed, autocomplete can be enabled. When the combobox loses focus, the entry is validated. DataValComboCheck.zip

DV0032 - Dependent Data Validation Combobox -- Double-click a cell that contains a data validation list, and a combobox appears -- font size can be set, more than 8 rows displayed, autocomplete can be enabled. List in second column is dependent on selection in first column. DataVal_Combo_Depend.zip

DV0020 - Data Validation Combobox-- Double-click on a cell that contains a data validation list, and a combobox appears -- font size can be set, more than 8 rows displayed, and autocomplete can be enabled. Macros must be enabled. DataValCombobox.zip

Buy the Premium Version

There is a premium version of this technique, and you can see the details here: Data Validation Multi Select Premium. It runs on a protected worksheet, and works with most dependent drop down lists.

In the premium version, a list box automatically appears when you select a cell that has a drop down list. You can set it to allow selection of a single item (button), or multiple items.

select a single item

select multiple items

For multiple selections, the listbox pre-selects any items that are already in the cell. It also has buttons to Clear all the selections, and select all the items.

The kit has a setup sheet, that lets you quickly customize the listbox, and details on how to add this technique to your own workbooks. See the details here: Data Validation Multi Select Premium.

More Data Validation Links

Data Validation Criteria Examples

Data Validation Tips

Hide Used Items in Dropdown List

Use List from Another Workbook

Dependent Drop Down Setup Choices

Create Dependent Lists

Flexible Dependent Drop Downs

Dependent Dropdowns from a Sorted List

 

 

 

Get weekly Excel tips from Debra

 

 

30 Excel Functions in 30 Days

 

 

 

DVMSP kit

 

 

 

 

pivot power premium

 

 

 

 

excel tools

 

 

pivot power premium

Last updated: September 9, 2019 10:47 AM