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.
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.
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:
Note: If the worksheet is protected, allow users to Edit Objects, and they will be able to use the combobox.
On Sheet1, type the lists that will be used in the data validation dropdowns:
Tip: Use the AutoFill feature to create the lists
The next step is to create the data validation dropdown lists. There are detailed instructions here: Data Validation -- Introduction
To add or edit the Combobox, open the Control Toolbox, and enter Design Mode:
To format the combo box, open the properties window:
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:
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.
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 '====================================
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.
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.
Last updated: November 15, 2017 2:34 PM