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