You can use 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.

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.

Double-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.

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

Video: Data Validation Drop Downs With Combo Box

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

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

The next step is to create the data validation dropdown lists.

  • 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.

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
  4. Click on the Combo box button, to activate that tool.
  5. 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. Select the combo box
  2. On the Control Toolbox, click the Properties button

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.

  1. 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 Control Toolbox, click the Exit Design Mode button

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.

Copy the Code

First, copy one of the code samples below.

  1. Code Sample 1 — will not work for data validation drop downs in merged cells
  2. Code Sample 2 — will work for both merged and non-merged cells.

NOTE: If your data validation lists are on a different sheet, use the instructions on this page:
Data Validation Combo Box – Named Ranges

Code Sample 1 — Does not work for merged cells

'==========================
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 = ws.Range(str).Address
      .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 Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet

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

errHandler:
  Application.EnableEvents = True
  Exit Sub

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

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
'====================================

Code Sample 2 — Works for merged cells and non-merged cells

'==========================
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
  Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim Tgt As Range
Set Tgt = Target.Cells(1, 1)
Set ws = ActiveSheet
On Error GoTo errHandler

If Tgt.Validation.Type = 3 Then
    Cancel = True
End If

Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
  With cboTemp
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
  End With
On Error GoTo errHandler

If Tgt.Validation.Type = 3 Then
    Application.EnableEvents = False
    str = Tgt.Validation.Formula1
    str = Right(str, Len(str) - 1)
    With cboTemp
      .Visible = True
      .Left = Tgt.Left
      .Top = Tgt.Top
      .Width = Target.Width + 15
      .Height = Target.Height + 5
      .ListFillRange = ws.Range(str).Address
      .LinkedCell = Tgt.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 Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet

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

errHandler:
  Application.EnableEvents = True
  Exit Sub

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

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
'====================================

Add Code to the Worksheet

Follow these steps, to add the copied code to the worksheet module:

  1. Right-click on the sheet tab, and choose 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.

Test the Code

  1. Double-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.

Download the Sample File

Download the zipped sample — Data Validation Combo box file DataValCombobox


Courtesy: contextures.com

Comments

  1. Sumit

    Dear Sir,

    Thank u so much for this, have searched umpteen sources but found the exact working solution here

    I have a query, The combo box is not working if the data for validation is in other sheet

    Please help with the VBA code modification

  2. Rra

    I am secretly cheating my Love, and I am excited!

    I was in deep love with her, in fact she was my world, and everything I could possibly think of involves her. I feel in love the moment I met her 12 years ago, since then I never look back, I grew personally and professionally in life and I was known by her name as #MrExcel, #DrExcel #ExcelExpert throughout these years and I truly enjoyed the joy ride to the core.

    Untill last month, I thought Mighty Excel is all I need, and it is perfect for any situation or need. But now, my way of looking at #data is turned 360° after I met my new friends #PowerBI, #QlikView, #Tableau & #Python. They helped me realize and understand how efficient and productive I could be and more importantly stay current if not futuristic!

    After started working with new friends, it has become super hard to go back to the old one. Sure, nostalgia kicks in once a while. But… you can not live in the past forever!

    My old friend #MsExcel is like #SuperMario my all-time favorite and my new friends #PowerBI, #QlikView, #Tableau & #Python are like #Minecraft & #PUBGMobile

    Am i going to leave Excel? No, it’s just that I will not be working extensively on Excel for #DataAnalytics and #Dashboards, since i could do most of the activities far more efficiently and quickly on other platforms, and i will continue harness the #automation capacities of Excel along with Python.

    What do you think? Do you also have similar experiences, please do share in comments below.

  3. Hugh Thomson

    Hi – this is great. Is it possible to edit the macro to allow the user to type any part of the text string to find a match. for example if the drop down is months of the year can it be made that if I start typing “gus”in the cell it selects “August”.

Leave a Reply

Your email address will not be published. Required fields are marked *

16 + 6 =

This site uses Akismet to reduce spam. Learn how your comment data is processed.