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

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.

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.

Video: Create an Excel Named Table

When you work with lists in Excel, use the built-in Table feature, to make it easy to sort and filter your data. This short video shows the simple steps

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.
____________