Home > Validation > Drop Downs > Combo Box Data Validation Combo Box using Named Ranges For Excel data entry, overcome the limitations of a data validation drop down list, by using a combo box, that refers to named ranges which contain the list items. NOTE: You can't use ActiveX controls on a Macintosh. Prof. Lee Townsend shows how to create drop downs using Form control combo boxes.

Introduction

You can use Data Validation to create a dropdown list of options in a cell.

However, the built-in drop down has several limitations:

• List font type can't be changed
• List font size cannot be increased
• Number of visible rows has a maximum of eight
• Data Validation drop down does not have an AutoComplete feature, to find matching items in the list as you start to type

Combo Box Benefits

To overcome these limitations, you can add a combo box to your worksheet, and use programming to make it appear for cells that contain a data validation list.

With this combo box feature added, you can:

• Double-click on a cell that contains a data validation list, and the combo box appears.
• Combo box font type and font size can be set
• More than 8 rows can be displayed
• Autocomplete can be enabled.

Note: If the worksheet is protected, allow users to Edit Objects, and they will be able to use the combobox.

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.

Set up the Workbook

Name the Sheets

Two worksheets are required in this workbook.

1. Delete all sheets except Sheet1 and Sheet2
2. Rename Sheet1 as ValidationSample
3. Rename Sheet2 as ValidationLists

Check the Zoom Level

IMPORTANT: Keep both sheets (ValidationSample and ValidationLists) at the same zoom setting, to avoid crashing Excel. There is a strange bug connected to combo boxes and zoom levels, in some versions of Excel.

Type the Lists

On the ValidationLists sheet, type the lists that will be used in the data validation dropdowns:

Tip: Use the AutoFill feature to create the drop-down list of weekdays and months

1. In cells A1:A7 type a list of weekdays
2. In cells C1:C12 type a list of months

Name the Lists

Next, you'll create names for the cells that contain the lists.

Worksheet Lists

If your lists are on the worksheet, but NOT in a named Excel table, follow these steps.

NOTE: There are Naming instructions here: Name a Range

1. Name the range A1:A7 as DayList
2. Name the range C1:C12 as MonthList

Excel Tables

If your lists are in named Excel tables, you'll need to create a second set of names.

Otherwise, the combo box lists will be empty.

1. First, create the named ranges, based on the table columns:
• Name the cells with day names as DayListA
• Name the cells with month names as MonthListA
2. Then, create a second set of names, based on that first names.
   • Create a named range DayList, based on the name DayListA
   • Create a named range MonthList, based on the name MonthListA

See detailed instructions on the Excel Worksheet Comboboxes page.

Create a Dropdown List

The next step is to create the dropdown lists. There are detailed instructions here: Excel Data Validation Introduction 

• TIP: When setting up the drop down lists, you can remove the check mark for "In-cell dropdown".
  • 

Data Validation Cells

• Cells B2:B12 have data validation lists with the source DayList. When a cell in this range is selected, a dropdown list of weekdays is available.
• Cells C2:C12 have data validation lists with the source MonthList. When a cell in this range is selected, a dropdown list of months is available. 

Add the Combo box

To add or edit the Combobox, follow these steps:

1. On the Ribbon, click the Developer tab. If you do not see the Developer tab, follow the steps here to show it.)
2. Click the Design Mode command

   

3. Click Insert, and under ActiveX Controls, click on the Combo box button, to activate that tool.

   

4. Click on an empty area of the worksheet, to add a combo box

Open the Properties Window

To format the combo box, open the properties window:

1. Right-click on the combo box, and click Properties

Change the Combo Box Properties

Name the Combo Box

1. In the Properties window, click in the Name box
2. Type a name for the combo box. In this example, the name is: TempCombo

Change the Font and Font Size

1. In the Properties window, click in the Font property, and click the ... button 

   

2. In the Font dialog box, select a font, font size, and other settings that you want for your combo box, then click OK.  

   

Set the Number of Rows

1. In the Properties window, click in the ListRows box
2. Type the number of rows that you want displayed in the dropdown. In this example, the setting is: 12

   

Turn on AutoComplete

1. In the Properties window, click in the MatchEntry property
2. From the dropdown list, select 1-frmMatchEntryComplete

   

Exit Design Mode

1. Close the Properties window
2. On the Developer tab, click the Design Mode button, to exit Design Mode.

Add the Code

Visual Basic for Applications (VBA) code is required to make the combo box appear when you double-click in a cell that contains a data validation list. See details in the next section, for How the Code Works.

Copy the following code

NOTE: For dates or numbers in the data validation, you can use the KeyDown code in the Code for Numbers section below.

'==========================
Private Sub Worksheet_BeforeDoubleClick _
  (ByVal Target As Range, _
    Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet

Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
  With cboTemp
  'clear and hide the combo box
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
  End With
On Error GoTo errHandler
  If Target.Validation.Type = 3 Then
    'if the cell contains 
      'a data validation list
    Cancel = True
    Application.EnableEvents = False
    'get the data validation formula
    str = Target.Validation.Formula1
    str = Right(str, Len(str) - 1)
    With cboTemp
      'show the combobox with the list
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top
      .Width = Target.Width + 5
      .Height = Target.Height + 5
      .ListFillRange = str
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
    'open the drop down list automatically
    Me.TempCombo.DropDown 
  End If
  
errHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub
'=========================================
Private Sub TempCombo_LostFocus()
  With Me.TempCombo
    .Top = 10
    .Left = 10
    .Width = 0
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With
End Sub
 '====================================
'Optional code to move to next cell 
'if Tab or Enter are pressed
'from code by Ted Lanham
'***NOTE: if KeyDown causes problems, 
'change to KeyUp
'Table with numbers for other keys 
'such as Right Arrow (39)
'https://msdn.microsoft.com/en-us/
  library/aa243025%28v=vs.60%29.aspx

Private Sub TempCombo_KeyDown(ByVal _
     KeyCode As MSForms.ReturnInteger, _
     ByVal Shift As Integer)
  Select Case KeyCode
    Case 9 'Tab
      ActiveCell.Offset(0, 1).Activate
    Case 13 'Enter
      ActiveCell.Offset(1, 0).Activate
    Case Else
        'do nothing
  End Select
End Sub
'====================================

To add this code to the worksheet:

1. Right-click on the ValidationSample sheet tab, and click View Code.

   

2. Choose Edit | Paste, to paste the code onto the sheet module, where the cursor is flashing.
3. Choose File | Close and Return to Microsoft Excel.

How the Code Works

Here are some details on how the code works.

Worksheet_BeforeDoubleClick

This code runs when a cell is double-clicked.

Variables are set for the active sheet, and the combo box named TempCombo -- be sure to use that name for your combo box.

Set ws = ActiveSheet

Set cboTemp = ws.OLEObjects("TempCombo")          

The combo box is hidden, and its linked cell and ListFillRange are cleared.

  With cboTemp
  'clear and hide the combo box
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
  End With

The data validation type for the Target cell (the active cell) is checked. If it is Type 3 (a drop down list), the rest of the code runs.

  If Target.Validation.Type = 3 Then

The str variable gets the data validation formula for the Target cell. For example: "=MonthList". Then, the equal sign is removed, by using the Right function. That leaves just the Range name for the data validation list -- "MonthList"

str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)

The combo box is made visible, an is positioned at the top left of the Target cell

    With cboTemp
      'show the combobox with the list
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top

The combo box width and height are determined by the Target cell's width and height, with a small amount added. You can change the "+5" to a different number:

 .Width = Target.Width + 5 
 .Height = Target.Height + 5          

The ListFillRange is changed to the str variable -- MonthList -- so the combo box will show the items from that named range.

      .ListFillRange = str

The LinkedCell is changed to the Target cell's address.

      .LinkedCell = Target.Address

The combo box is activated, and the drop down list is opened.

    cboTemp.Activate
    Me.TempCombo.DropDown 

  End If

TempCombo_LostFocus

This code runs when you exit the combo box

The combo box is hidden, moved to the top left of the worksheet, and its linked cell and ListFillRange are cleared.

  With Me.TempCombo
    .Top = 10
    .Left = 10
    .Width = 0
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With

Test the Code

IMPORTANT: Keep both sheets (ValidationSample and ValidationLists) at the same zoom setting, to avoid crashing Excel. There is a strange bug connected to combo boxes and zoom levels.

1. Double-click on one of the cells that contains a data validation list.
2. The combo box will appear
3. Click the down arrow, and select an item from the combo box drop down list, or start typing, and the item will autocomplete

   

4. Click on a different cell, to select it
5. The selected item appears in previous cell, and the combo box disappears.

   

Adjust the Combo Box Properties

If you decide to change the combo box properties later, it might be difficult to find the combo box on the worksheet, because the code changes its width to zero.

Follow these steps to locate the combo box, and adjust its properties:

1. On the Ribbon, click the Developer tab.
2. Click the Design Mode command
3. Click the Properties command.

   

4. In the Properties window, select TempCombo from the drop down list

   

5. Adjust the properties that you want to change

   NOTE: The combo box width and height are set in the macro -- changing these values in the property window will not have a long term effect. Edit the code, if you want the size to change.

6. When finished, close the Property window, and click the Design button, to exit Design mode.

Combo Box Code For Numbers

The values that you select in a combo box are treated as text, and that can cause problems if your drop down list contains numbers (including dates and times).

In the screen shot below, a time has been selected from the combo box list. Even though the cell is formatted for Time, the cell contains a long decimal number. The entry is really text, not a number, so that's why the number formatting is not applied to it.

Change Text to Real Numbers

If you're using the combo box for numbers, you can add a bit more code to your workbook.

• Keyboard: If you use the Tab or Enter key to leave the combo box, add the code from the Using the Keyboard section below
• Mouse: If you use the Mouse to leave the combo box, add the code from the Using the Mouse section below

NOTE: If you alternate between using the keyboard, and using the mouse, you can add the code from both sections.

Using the Keyboard

If you use the Tab or Enter key to leave the combo box, use the following code, instead of the TempCombo_KeyDown code above.

Then, after you select a number (or date or time) in the combo box drop down list, press the Enter key or the Tab key, to move to the next cell.

This code will run, and it will change the text value to a real number. Then it will activate the cell below, if you pressed Enter, or it will activate the cell to the right, if you pressed Tab.

'====================================
'Optional code to move to next cell 
'if Tab or Enter are pressed
'from code by Ted Lanham
'***NOTE: if KeyDown causes problems, 
'change to KeyUp

Private Sub TempCombo_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
'move to next cell on Enter and Tab
Dim varVal As Variant
On Error Resume Next
 'change text value to number, if possible
varVal = --ActiveCell.Value
If IsEmpty(varVal) Then
  varVal = ActiveCell.Value
End If

Select Case KeyCode
  Case 9  'tab
    ActiveCell.Value = varVal
    ActiveCell.Offset(0, 1).Activate
  Case 13 'enter
    ActiveCell.Value = varVal
    ActiveCell.Offset(1, 0).Activate
  Case Else
    'do nothing
End Select
End Sub
'====================================

Using the Mouse

If you use the Mouse to leave the combo box, use the following code, instead of the TempCombo_LostFocus code above.

Then, after you select a number (or date or time) in the combo box drop down list, press the Enter key or the Tab key, to move to the next cell.

This code will run, and it will change the text value to a real number.

'====================================
Private Sub TempCombo_LostFocus()
''if you use mouse to select in
''  combo box, use this code
'' to change text to real numbers
Dim varVal As Variant
On Error Resume Next
  If Not myCell Is Nothing Then
     'change text value to number,
     '  if possible
    If myCell.Value <> "" Then
      varVal = --myCell.Value
      If IsEmpty(varVal) Then
        varVal = myCell.Value
      End If
      myCell.Value = varVal
    End If
    Set myCell = Nothing
  End If

  With Me.TempCombo
    .Top = 10
    .Left = 10
    .Width = 0
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With
End Sub
'====================================

Dependent Lists With INDIRECT

If some of your drop down lists have dependent data validation, using a simple INDIRECT formula, you can add a few lines of code to handle those lists.

In the existing code on the ValidationSample sheet module, look for the following lines:

    Application.EnableEvents = False
    str = Target.Validation.Formula1
    str = Right(str, Len(str) - 1)

Below those lines, add the following lines of code, to handle simple INDIRECT formulas, such as =INDIRECT(E2)

This is used for the City drop down list in the Dependent Combo sample file below.

    'for simple INDIRECT function (English)
    ' e.g. =INDIRECT(B2)
    'will create dependent list of items
    If Left(str, 4) = "INDI" Then
      lSplit = InStr(1, str, "(")
      str = Right(str, Len(str) - lSplit)
      str = Left(str, Len(str) - 1)
      str = Range(str).Value
    End If

Dependent Drop Downs- Dynamic Lists

For drop down lists that are based on dynamic named ranges, add a few more lines of code to handle those lists. Excel won't let you use some dynamic range names in the ListFillRange property for the combo box, so this workaround adds all the items from the dynamic list.

• The code checks the ListFillRange property, to see if it matches the name of the drop down list's source.
• If they don't match, the code sets a range, based on the sheet name and address for the dynamic named range.
• Then, the ListFillRange is set to that sheet name and address.

Download the sample file, to see the full code for the dependent lists with dynamic ranges:

    .ListFillRange = str
      If .ListFillRange <> str Then
        'for dynamic named ranges
        str = Target.Validation.Formula1
        str = Right(str, Len(str) - 1)
        Set wb = ActiveWorkbook
        Set nm = wb.Names(str)
        Set wsNm = wb.Worksheets _
          (nm.RefersToRange.Parent.Name)
        Set rng = wsNm.Range _
          (nm.RefersToRange.Address)
        .ListFillRange = "'" & wsNm.Name _
              & "'!" & rng.Address
      End If

Get the Sample Files

1. To test the combo box code, you can download the zipped sample file.

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

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

4. This sample has a Dependent Combo box, in a column with merged cells.

More Data Validation Tutorials

Data Validation Basics

Create Dependent Lists

Drop Down List Types

Combo box Resources

Use a List from Another Workbook

Data Validation Combo Box

Data Validation Combo Box - Click