Data Validation Combo Box in Excel Table

There are instructions on my Contextures website for using a combo box with data validation cells. Click on a cell that contains a data validation list, and the combo box appears. It takes a bit of programming, but has several advantages over a data validation list drop down:

  • a combo box’s font size can be set
  • more than 8 rows can be displayed
  • autocomplete can be enabled

datavalidationcombobox01

Exit the Combo Box

In the first version of the data validation combo box, you had to double-click on a cell, to make the combo box appear. In the next version, you simply clicked on a cell with a data validation list, and the combo box appeared.

Then, Ted Lanham suggested code that would allow you to exit the combo box by pressing the Enter key or the Tab key, so you didn’t even have to touch the mouse to make the combo box appear. The mind control version is still on the drawing board.

Here’s Ted’s original code, that moves one cell to the right, if the Tab key is pressed, and one cell down, if Enter is pressed, while the combo box is active.

'====================================
'Optional code to move to next cell if Tab or Enter are pressed
'from code by Ted Lanham
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
'====================================

 

Working with Excel Tables

In Excel 2007, the Excel Table feature was added, replacing the List feature from Excel 2003. The last cell in the Excel Table has a handle that you can drag, to adjust the size of the table.

ExcelTableEnd

The Enter key works the same in an Excel Table, always moving you to the cell below (unless you’ve changed your settings). However, the Tab key works a bit differently in an Excel Table:

  • If you press the Tab key in the last column of an Excel table, you don’t go to the right — you go down one row, and to the first column of the table.
  • If you press the Tab key in the last cell of an Excel Table, a new row is added at the bottom of the table, and you move to the first column in the new row.

Changing the Combo Box Code

To accommodate for data validation combo boxes that are in Excel Tables, I’ve changed the code so it works the same as the regular Tab key.

Private Sub TempCombo_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
Dim tb As ListObject
Dim lCols As Long
Dim lCol As Long
Dim lRows As Long
Dim lRow As Long
Dim lColStart As Long
Dim lRowStart As Long

On Error Resume Next
Set tb = ActiveCell.ListObject
lCols = tb.ListColumns.Count
lCol = tb.ListColumns(lCols).Range.Column
lRows = tb.ListRows.Count
lRow = tb.ListRows(lRows).Range.Row
lColStart = tb.ListColumns(1).Range.Column
lRowStart = tb.ListRows(1).Range.Row - 1

'Hide combo box and move to next cell on Enter and Tab
Select Case KeyCode
  Case 9 'tab
    If ActiveCell.Column = lCol Then
      If ActiveCell.Row = lRow Then
        tb.Resize Range(Cells(lRowStart, lColStart), Cells(lRows + 2, 3))
      End If
      ActiveCell.Offset(1, -(lCol - lCols)).Activate
    Else
      ActiveCell.Offset(0, 1).Activate
    End If
  Case 13 'enter
    ActiveCell.Offset(1, 0).Activate
  Case Else
    'do nothing
End Select

End Sub

The Enter code hasn’t changed — just the Tab key code. The code tries to set a variable for the ListObject, then calculate the number of rows and columns in the table. The heading row isn’t counted in the ListRows, so the code adjusts for that.

  • If the active cell is in the last column, pressing Tab will take you to the first cell in the next row.
  • If the active cell is in the last column and last row, pressing Tab will create a new row, and will take you to the first cell in the new row.

Download the Sample File

To see the code, and test the Tab key behaviour, you can download the data validation combo box for Excel Tables workbook. The file is zipped, and you’ll have to enable macros when you open the file in Excel.

____________

You may also like...

4 Responses

  1. Rick Rothstein (MVP - Excel) says:

    Just noting that Ted Lanham’s code for exiting the ComboBox can be reduced to a one-liner…

    Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    ActiveCell.Offset(-(KeyCode = 13), -(KeyCode = 9)).Activate
    End Sub

    Personally, though, I prefer to use built-in VB constants rather than use “magic” numbers, so I would write this code like this instead…

    Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    ActiveCell.Offset(-(KeyCode = vbKeyReturn), -(KeyCode = vbKeyTab)).Activate
    End Sub

  2. Katrina Osby says:

    I’ve been playing with the “combo box with data validation” cells spreadsheet. I really like the way it looks. My problem is that I have a very long list of choices in my spreadsheet, and want the list on a different worksheet. Is it possible to do this with the data validation using data from a different worksheet? Thanks! Katrina

  3. JP says:

    Hi, I have the same question as Katrina has. I have multiple long validation lists is a second sheet.
    Is there a way to get this code to work with validation ranges on a different sheet in the same workbook?
    (I’m using Excel 2010)

    Best regards,

    JP

    • @JP, in the code, the ws variable is set to the active sheet:

      Set ws = ActiveSheet

      You could change that, to refer to the sheet where your list is:

      Set ws = Worksheets(“MyLists”)

Leave a Reply to Katrina Osby Cancel reply

Your email address will not be published. Required fields are marked *