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.
Set up the Workbook
Name the Sheets
Two worksheets are required in this workbook.
- Delete all sheets except Sheet1 and Sheet2
- Rename Sheet1 as ValidationSample
- Rename Sheet2 as ValidationLists
Check the Zoom Level
IMPORTANT: Set the ValidationLists sheet at 100% zoom, to avoid crashing Excel. There is a strange bug connected to combo boxes and zoom levels.
You can adjust the zoom level on the ValidationSample sheet, but do not change ValidationLists from 100%.
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 lists
- In cells A1:A7 type a list of weekdays
- In cells C1:C12 type a list of months
Name the lists:
- Name the range A1:A7 as DayList
- Name the range C1:C12 as MonthList
Create a Dropdown List
The next step is to create the dropdown lists.
- Cells C2:C12 have data validation lists with the source DayList. 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 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:
- On the Ribbon, click the Developer tab. If you do not see the Developer tab, go to Excel Optiona and enable it from there
- Click the Design Mode command
- Click Insert, and under ActiveX Controls, 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:
- Right-click on the combo box, and click Properties
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, then 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 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.
NOTE: In the code, the combo box width and height are determined by the active 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
Copy the following code
'==========================
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 Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = True
If Application.CutCopyMode Then
'allow copying and pasting on the worksheet
GoTo errHandler
End If
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
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
'====================================
To add this code to the worksheet:
- Right-click on the ValidationSample sheet tab, and click 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
IMPORTANT: Leave the ValidationLists sheet at 100% zoom, to avoid crashing Excel. There is a strange bug connected to combo boxes and zoom levels.
- 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, or start typing, and the item will autocomplete
- Click on a different cell, to select it
- 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:
- On the Ribbon, click the Developer tab.
- Click the Design Mode command
- Click the Properties command.
- In the Properties window, select TempCombo from the drop down list
- Adjust the properties that you want to changeNOTE: 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.
- When finished, close the Property window, and click the Design button, to exit Design mode.
Download the Sample File
To test the combo box code, you can download the zipped sample files DataValComboboxSheet_NamedRange.
Also Read:
- How to create dependent drop down lists containing unique distinct values in excel
- 12 Basic Shortcuts That Will Make You An Excel Guru And Add Hours To Your Life
- How to VLOOKUP with Multiple Criteria in Multiple Column
Courtesy: contextures.com