Contextures

Excel Data Validation Combo box Click

Instead of Excel drop down list show a combo box. Allows autocomplete, bigger text. Click a cell to make the combo box appear.

There is also a premium version of this technique. It runs on a protected worksheet, and works with most dependent drop down lists.

Introduction

You can use Excel Data Validation to create a dropdown list of options in a cell. However, the list font can't be changed, nor can the number of visible rows, which has a maximum of eight. Also, Data Validation doesn't have an AutoComplete feature, which finds matching items in the list as you start to type.

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

drop down list

To overcome these limitations, you can add a combo box to your worksheet, and use programming to make it appear in cells that contain a data validation list. Click on a cell that contains a data validation list, and the combo box appears. The combo box's font size can be set, more than 8 rows can be displayed, and autocomplete can be enabled.

If you would prefer to see the combo box only when you double-click on a data validation cell, please use the instructions at one of the following pages:

Data Validation -- Combo Box     
Data Validation -- Combo Box - Named Ranges

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

edit objects

Create a Data Validation Dropdown List

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

Tip: Use the AutoFill feature to create the lists

  1. In cells K2:K8 type a list of weekdays
  2. In cells M2:M13 type a list of months 

lists of weekdays and months

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

  • Cells C2:C12 have data validation lists with the source K2:K8. When a cell in this range is selected, a dropdown list of weekdays is available.
  • Cells D2:D12 have data validation lists with the source M2:M13. When a cell in this range is selected, a dropdown list of months is available. go to top

dropdown list of months

Add the Combo box

To add or edit the Combobox, open the Control Toolbox, and enter Design Mode:

  1. Choose View | Toolbars
  2. Select Control Toolbox
  3. Click the Design Mode button

    control toolbox

  4. Click on the Combo box button, to activate that tool.

    combo box button

  5. Click on an empty area of the worksheet, to add a combo box go to top

Open the Properties Window

To format the combo box, open the properties window:

  1. Select the combo box
  2. On the Control Toolbox, click the Properties button

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

    name the combo box

Change the Font and Font Size

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

    font button

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

change the font settings

  1. Click OK  go to top

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

match entry property

Exit Design Mode

  1. Close the Properties window
  2. On the Control Toolbox, click the Exit Design Mode button

exit design mode

Add the Code

Visual Basic for Applications (VBA) code is required to make the combo box appear when you click in a cell that contains a data validation list. Copy the following code:

NOTE: If your worksheet has data validation in merged cells, use the code in sample file #2, in the download section below.

'=========================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
On Error GoTo errHandler

If Target.Count > 1 Then GoTo exitHandler

Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
If cboTemp.Visible = True Then
  With cboTemp
    .Top = 10
    .Left = 10
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With
End If

  On Error GoTo errHandler
  If Target.Validation.Type = 3 Then
    'if the cell contains a data validation list
    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 + 15
      .Height = Target.Height + 5
      .ListFillRange = ws.Range(str).Address
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
    'open the drop down list automatically
    Me.TempCombo.DropDown 
  End If

exitHandler:
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Exit Sub
errHandler:
  Resume exitHandler 

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 sheet tab, and choose View Code.

    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. go to top

Test the Code

  1. Click on one of the cells that contains a data validation list.
  2. The combo box will appear
  3. Select an item from the combo box dropdown list
  4. Click on a different cell, to select it
  5. The selected item appears in previous cell, and the combo box disappears. go to top

test the code

TempCombo_KeyDown Code For Numbers

The values that you select in a combo box are treated as text, so 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, and even though the cell is formatted for Time, it appears in the cell as a long decimal number. The entry is really text, not a number, so the number formatting does not affect it.

times in drop down

To send the numbers to the worksheet as real numbers, instead of text, 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.

'====================================
'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)
  'change text value to number, if possible 
  On Error Resume Next
  
  Select Case KeyCode
    Case 9 'Tab - change text to number, move right
      ActiveCell.Value = --ActiveCell.Value
      ActiveCell.Offset(0, 1).Activate
    Case 13 'Enter - change text to number, move down
      ActiveCell.Value = --ActiveCell.Value
      ActiveCell.Offset(1, 0).Activate
    Case Else
        'do nothing
  End Select
End Sub
'====================================

Download the Sample File

  1. Combo Box Click - Download the zipped Combo Box Click sample file for this tutorial. It contains the sample code posted above. The source lists are on the same sheet as the data validation cells.
  2. Merged Cells - If your workbook has data validation in merged cells, download the zipped Combo Box Merged Click sample file. In this sample file, the data validation is based on named lists on a different sheet.

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.

DVMSP

DVMSP popup

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 -- Combo Box

Data Validation -- Combo Box - Named Ranges

Data Validation Basic

Create Dependent Lists

Use a List from Another Workbook

Criteria Examples

Data Validation Tips

Search Contextures Sites

 

DVMSP

 

 

Pivot Power Premium

 

 

Excel Data Entry Popup List

 

 

 

Excel UserForms for Data Entry

 

Last updated: August 12, 2016 4:34 PM