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
- In cells K2:K8 type a list of weekdays
- 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:
- Choose View | Toolbars
- Select Control Toolbox
- Click the Design Mode button
- Click on the Combo box button, to activate that tool.
- 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:
- Select the combo box
- On the Control Toolbox, click the Properties button
Change the Combo box Properties
Name the Combo box
- In the Properties window, click in the Name box
- Type a name for the combo box. In this example, the name is: TempCombo
Change the Font and Font Size
- In the Properties window, click in the Font property, and click the … button
- In the Font dialog box, select a font, font size, and other settings that you want for your combo box.
- Click OK
Set the Number of Rows
- In the Properties window, click in the ListRows box
- Type the number of rows that you want displayed in the dropdown. In this example, the setting is: 12
Turn on AutoComplete
- In the Properties window, click in the MatchEntry property
- From the dropdown list, select 1-frmMatchEntryComplete
Exit Design Mode
- Close the Properties window
- 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.
- Code Sample 1 — will not work for data validation drop downs in merged cells
- 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:
- Right-click on the sheet tab, and choose View Code.
- Choose Edit | Paste, to paste the code onto the sheet module, where the cursor is flashing.
- Choose File | Close and Return to Microsoft Excel.
Test the Code
- Double-click on one of the cells that contains a data validation list.
- The combo box will appear
- Select an item from the combo box dropdown list
- Click on a different cell, to select it
- 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