|
Home > Validation > Drop Downs > Combo Box Data Validation Combo box ClickInstead of an Excel data validation drop down list, with tiny print, make data entry easier with a combo box. With the combo box, you can have your entries autocomplete, and set a bigger text size. Click a data validation cell to make the combo box appear.
|
You can use Excel Data Validation to create a dropdown list of options in a cell. This is a helpful features, but has some limitations. For example:
In the screen shot below, you can see the standard drop down list, with 8 pt font, and 8 months showing. You'll have to scroll down to see the last 4 months of the year.

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:
Data Validation -- Combo Box
Data Validation -- Combo Box - Named Ranges
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:






If the combo box worksheet is protected, be sure to allow users to Edit Objects, and they will be able to use the combobox.
In the screen shot below, I've checked that setting in the Protect Sheet dialog box.

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
'====================================
Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.
Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!
Data Validation -- Combo Box - Named Ranges
Last updated: July 13, 2024 3:23 PM