To overcome the limitations of a data validation drop down list, you can add a combo box to your worksheet, and use programming to make it appear in cells that contain a data validation list
NOTE: You can't use ActiveX controls on a Macintosh. Prof. Lee Townsend shows how to create drop downs using Form control combo boxes.
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.
Instead of clicking the cell's drop down arrow, 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.
To see how the combo box works, and appears when you double-click a data validation cell, watch this short video. The written steps are below the video, and the full transcript is available below.
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:
Change the Font and Font Size
Turn on AutoComplete
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.
First, copy one of the code samples below.
NOTE: If your data validation lists are on a different sheet,
use the instructions on this page:
Data Validation Combo Box - Named Ranges
'========================== 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 '====================================
'========================== 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 '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 '====================================
Follow these steps, to add the copied code to the worksheet module:
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 '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) '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 '====================================
Here is the full transcript for the Autocomplete Entries With Excel Drop Down List video, shown above.
'==========================
To make it easy for people to enter data in Excel, you can create drop down lists of items.
In this cell, we have a list of weekdays. I can click on that Arrow, click on a weekday, and it fills in the cell.
The same thing for months. Here's a list. I can't see all of the months, though. It only shows 8 items at a time, but I can scroll down and click on December and that's in the cell.
Those lists are on another worksheet, so I've just typed the days here, and the months here.
And then use data validation, which you can find on the Data tab, to set that up.
There are a few limitations though. If we look at the list, the font is very small and you can't change it.
It's not too bad here at 100% zoom, but if you start going down to lower zoom levels, so that you can see more of your worksheet, it might be hard, or almost impossible, to read these lists.
And as we saw for the months, you can only see eight items at a time.
It's not too bad here because we only have 12 months, but for a longer list you might need to do quite a bit of scrolling up and down to find items.
This doesn't auto complete. Data validation doesn't help you fill in the word.
Excel might help you, if it can copy something that you've already entered above, but the data validation itself won't help me type January.
If I start typing, I have to fill in the whole word and move on to the next cell.
So data validation is helpful, but I've come up with a work around that you can download on my website and it uses a combo box over cells that have a data validation drop down.
In this corner, hidden away, is a little combo box, and it pops up over a cell, when you double click on it.
So if I double click here, the Combo box comes out of hiding, goes over this cell, and picks up the same data validation list that the cell uses.
So when I click the arrow, I'll see the list of weekdays. I can click on one, press Enter to go to the next cell.
I'll double click again, and for this one I could start typing T, and it fills in Tuesday, but if I wanted Thursday, I'll type in h, and then press Enter.
So it helps with the typing, and if we look at the months, I've changed the number of rows that show, so we can see 12.
You can adjust that setting, as well as the font size, and select. And again here, we can type.
So if I want to type August, just have to type two letters and it's filled in for me.
To see the Combo box, I'm going to double click on a cell and then go to the Developer tab on the ribbon, and there's a Design Mode button.
When I click that, I'm able to make changes to that Combo box. I can click on the Properties button and here are all the things you could change about that Combo box.
So you get a lot more control over it than you have over data validation.
So you could come down here, and go to the font. Instead of Arial font, you could change the font, and the font size, to whatever suits you.
And here's where I changed the list rows. This is 12 you could make that a larger number. You'd want to pick a number that's going to fit on the screen. You don't want anything that's too huge, but you can adjust that setting.
And then when you're done making changes, turn off Design Mode, and you're ready to go again.
So if you download the sample file from my website, you'll be able to copy it all into your workbook.
Copy the Combo box and the code onto the worksheet. So if I right click View Code, you'll see that there's code that runs, when you double click the cell.
You don't have to understand all this code, you would just have to get it into your workbook.
For more Excel tips and tutorials, and to download the sample file for this video, please visit my Contextures website, at www.contextures.com
Get the zipped sample for this tutorial -- Data Validation Combo box file
Last updated: April 4, 2022 7:14 PM