Search Contextures Sites

 

Contextures
Excel news
by email

 

 

30 Excel Functions in 30 Days

 

 

 

Learn how to create Excel dashboards.

 

 

 

 

Your worksheet formulas can create traffic-light charts, highlight chart elements, assign number formats, and more.

 

 

 

 

 

Learn how to create Excel dashboards.

 

 

Learn how to create Excel dashboards.

 

 

Learn how to create Excel dashboards.

 

 

Live-link your Excel dashboards to nearly any web data.

 

 

 

 

Data Validation Combo Box using Named Ranges

Video: Data Validation Drop Downs With Combo Box
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  
How the Code Works
Test the Code  
Adjust the Combo Box Properties
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.

standard data validation

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.

NOTE: You canít use ActiveX controls on a Macintosh. Prof. Lee Townsend shows how to create dependent drop downs using Form control combo boxes.

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.

Video: Data Validation Drop Downs With Combo Box

To see how the combo box works, and appears when you double-click a data validation cell, watch this short video.

Set up the Workbook

Name the Sheets

Two worksheets are required in this workbook.

  1. Delete all sheets except Sheet1 and Sheet2
  2. Rename Sheet1 as ValidationSample
  3. Rename Sheet2 as ValidationLists

rename the sheets

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

  1. In cells A1:A7 type a list of weekdays
  2. In cells C1:C12 type a list of months

data validation lists

Name the lists (there are Naming instructions here: Name a Range):

  1. Name the range A1:A7 as DayList
  2. Name the range C1:C12 as MonthList    

Create a Dropdown List

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. 

drop down lists

Add the Combo box

To add or edit the Combobox, follow these steps:

  1. On the Ribbon, click the Developer tab. If you do not see the Developer tab, follow the steps here to show it.)
  2. Click the Design Mode command
  3. Design Mode command

  4. Click Insert, and under ActiveX Controls, click on the Combo box button, to activate that tool.
  5. Combo Box on ActiveX Controls

  6. 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:

  1. Right-click on the combo box, and click Properties

open the properties window

Change the Combo Box Properties

Name the Combo Box

  1. In the Properties window, click in the Name box
  2. Type a name for the combo box. In this example, the name is: TempCombo

drop down lists

Change the Font and Font Size

  1. In the Properties window, click in the Font property, and click the ... button 
  2. drop down lists

  3. In the Font dialog box, select a font, font size, and other settings that you want for your combo box, then click OK.  

    drop down lists

Set the Number of Rows

  1. In the Properties window, click in the ListRows box
  2. Type the number of rows that you want displayed in the dropdown. In this example, the setting is: 12

    drop down lists

Turn on AutoComplete

  1. In the Properties window, click in the MatchEntry property
  2. From the dropdown list, select 1-frmMatchEntryComplete

    drop down lists

Exit Design Mode

  1. Close the Properties window
  2. On the Developer tab, click the Design Mode button, to exit Design Mode.

    Design Mode command

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: See details in the next section, for How the Code Works

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:

  1. Right-click on the ValidationSample sheet tab, and click View Code.
  2. View Code

  3. Choose Edit | Paste, to paste the code onto the sheet module, where the cursor is flashing.
  4. Choose File | Close and Return to Microsoft Excel.

How the Code Works

Here are some details on how the code works.

Worksheet_BeforeDoubleClick

This code runs when a cell is double-clicked.

Variables are set for the active sheet, and the combo box named TempCombo -- be sure to use that name for your combo box.

Set ws = ActiveSheet

Set cboTemp = ws.OLEObjects("TempCombo")          

The combo box is hidden, and its linked cell and ListFillRange are cleared.

  With cboTemp
  'clear and hide the combo box
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
  End With

The data validation type for the Target cell (the active cell) is checked. If it is Type 3 (a drop down list), the rest of the code runs.

  If Target.Validation.Type = 3 Then

The str variable gets the data validation formula for the Target cell. For example: "=MonthList". Then, the equal sign is removed, by using the Right function. That leaves just the Range name for the data validation list -- "MonthList"

str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)

The combo box is made visible, an is positioned at the top left of the Target cell

    With cboTemp
      'show the combobox with the list
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top

The combo box width and height are determined by the Target 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

The ListFillRange is changed to the str variable -- MonthList -- so the combo box will show the items from that named range.

      .ListFillRange = str

The LinkedCell is changed to the Target cell's address.

      .LinkedCell = Target.Address

The combo box is activated, and the drop down list is opened.

    cboTemp.Activate
    Me.TempCombo.DropDown 

  End If

SelectionChange

This code runs when a cell is selected

Variables are set for the active sheet, and the combo box named TempCombo -- be sure to use that name for your combo box.

Set ws = ActiveSheet
Set cboTemp = ws.OLEObjects("TempCombo")

If a range has been copied, the code does not run, to allow copying and pasting.

If Application.CutCopyMode Then
  GoTo errHandler
End If

The combo box is hidden, moved to the top left of the worksheet, and its linked cell and ListFillRange are cleared.

  With cboTemp
    .Top = 10
    .Left = 10
    .Width = 0
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With

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.

  1. Double-click on one of the cells that contains a data validation list.
  2. The combo box will appear
  3. Select an item from the combo box dropdown list, or start typing, and the item will autocomplete
  4. drop down lists

  5. Click on a different cell, to select it
  6. The selected item appears in previous cell, and the combo box disappears.

    drop down lists

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:

  1. On the Ribbon, click the Developer tab.
  2. Click the Design Mode command
  3. Click the Properties command.
  4. drop down lists

  5. In the Properties window, select TempCombo from the drop down list
  6. drop down lists

  7. Adjust the properties that you want to change
  8. NOTE: 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.

  9. 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 file.

More Data Validation Tutorials

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  

 

Privacy Policy

 

Contextures Inc., Copyright ©2014
All rights reserved.

 

 

Last updated: December 7, 2014 1:27 PM