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.

Multi-Column Combo: This sample file has a combo box with 2 columns, so you can see more information about the item that you're selecting. For example, see the product code in one column, and product name in the other column. There are details on my Contextures blog.

Other Combo Box Examples

DV0075 - Data Validation Combo Dependent Merged --
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. City column is merged, and is dependent on selection in Region column, using INDIRECT and SUBSTITUTE formula. Click here to download: datavalcombomergedepend.zip

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

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

 

Last updated: July 12, 2021 7:22 PM