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.

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.

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

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)