Search Contextures Sites
Set up the Workbook
Create a Dropdown List
Add the Combo box
Open the Properties Window
Change the Combo box Properties
Exit Design Mode
Add the Code
Test the Code
Download the Sample File
More Data Validation Tutorials
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.
Two worksheets are required in this workbook.
- Delete all sheets except Sheet1 and Sheet2
- Rename Sheet1 as ValidationSample
- Rename Sheet2 as ValidationLists
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 (there are Naming instructions here: Name a Range):
The next step is to create the dropdown lists. There are detailed instructions here: Excel Data Validation Introduction
- 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.
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
To format the combo box, open the properties window:
- Select the combo box
- On the Control Toolbox, click the Properties button
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.
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
- Close the Properties window
- On the Control Toolbox, click the Exit Design Mode button
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 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 Dim wsList As Worksheet Set ws = ActiveSheet Set wsList = Sheets("ValidationLists") Cancel = True 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 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:
- 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.
To test the combo box code, you can download the zipped sample file.
Data Validation Basics
Data Validation - Create Dependent Lists
Data Validation - Dependent Dropdowns from a Sorted List
Data Validation - Dependent Lists With INDEX
Hide Previously Used Items in a Dropdown List
Data Validation - Display Messages to the User
Data Validation - Display Input Messages in a Text Box
Data Validation - Use a List from Another Workbook
Data Validation Criteria Examples
Data Validation Custom Criteria Examples
Data Validation Tips
Data Validation Documentation
Data Validation Combo Box
Data Validation Combo Box - Named Ranges
Data Validation Combo Box -- Click
Data Validation - Add New Items
Contextures Inc., Copyright ©2013
All rights reserved.
Last updated: June 3, 2013 6:54 PM