Data validation provides a mean to enable, and optionally, restrict your users to select values for a cell from a dropdown list. But what if you would like to present such a list to the user where the list has multiple columns? For example, let’s say you want the user to choose from a list of codes, but would like a second column to describe the code.
In place of data validation, we can use a combobox on a sheet. It remains hidden until the focus moves to a cell within a range where you would like the multi-column combobox to appear.
In our example, let’s use an Excel table, and within our data, there are two columns (e.g. A and B) where we would like to dynamically display a combobox and that there are different lists associated with each column. We are going to use a single ActiveX combobox for both columns A and B. We would like to be able to either click an item from the combobox list or type a value with “Type ahead” functionality. We also want to be able to Tab through the cells in the table, automatically displaying the combobox if we land in columns A or B. As well, we want to be able to press the ESC key to hide the combobox if visible.
There are a few properties of the combobox we will need to use, some only within VBA code:
| Property | Initial Setting |
| Visible | False |
| ListFillRange | Can leave blank, used in VBA code |
| LinkedCell | Can leave blank, used in VBA code |
| MatchEntry | 1 – frmMatchEntryComplete |
| MatchRequired | True |
| ListWidth | Wide enough to accommodate two columns of text |
| Top | Changed in VBA code (no need to set an initial value) |
| Left | Changed in VBA code (no need to set an initial value) |
| Height | Changed in VBA code (no need to set an initial value) |
| Width | Changed in VBA code (no need to set an initial value) |
Download the sample workbook. The Data worksheet contains the data where we want the combobox to appear within columns A and B. The Combobox D
ata worksheet holds two named ranges of data to populate the combobox.