With Excel VBA programming, you can add a Combo Box to the worksheet, to show a data validation list. Usually a single column combo box is enough, like this list of weekdays.
In some cases, it's helpful to have a multi-column combo box. For example, instead of a single-column list with product codes, you could show the product names in another column.
When you select an item from the drop down list, only the product code is added to the worksheet cell.
The Lookup Lists
On another worksheet, there are two named ranges – ToolList (A1:A7) and ToolListFull (A1:B7).
The ToolListFull range is used as the ListFillRange for the Combo Box, and ToolList is used for the data validation list.
Combo Box Settings
To show two columns in the combo box, change its ColumnCount setting to 2.
When you double-click on a data validation cell, event code runs, that finds the list used in the data validation cell. Then, "Full" is added to that name, to find the list for the combo box.
Download the Sample Workbook
To see the multi-column combo box, and the code, you can download the Multi-Column Combo Box sample file.
_______________
[...] levels (or any other metric levels). Go ahead and study the technique from Peltier's blog. Multi-Column Combo Boxes in ExcelDebra shows us a ridiculously simple way to show multi-column combo boxes in Excel. The trick is to [...]
Hi All,
Can anybody tell me how to implement vba with combobox. Lets come to my required scenario. I have a combobox in Excel sheet. My requirement is if i selected an item from combobox, the data should come for that selected item only. Can anybody solve my problem. All kinds of suggessions are appreciable.
Cheers,
Tarak
Hi, this is exactly what I want to do, but for some reason when I download your sample workbook, I don't see two columns in my drop down list. I've enabled the macros. Any ideas why it might not work for me?
I was trying to create a sheet for easy data entry of client evaluation of services. I wanted a dropdown that contained text (excellent,good,OK, poor, unacceptable)but also needed a numerical score to be recorded based on their choice.
I read a zillion articles and watched a bunch of tutorials but since I don't know VBA most of it was over my head. (I know a formula probably would have been easier but I wanted to figure out a different way)
So on Sheet2 I listed the words in column A and the scores (1-5) in column B. On Sheet1 in column A was the question (i.e. How respectfully were you treated?). In column B I put in a plain combobox with the source data being the list of words from Sheet2 and then for the linked cell chose Sheet1 in column C just to the right of the combobox. This works and I didn't do any tricky VBA code thing. The only drawback is I don't know how to avoid creating each combobox individually. Is there an easier non VBA way of doing this?