Multi-Column Excel Combo Box

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.

comboboxmulticolumn01

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.

comboboxmulticolumn02

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

comboboxmulticolumn03

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.

comboboxmulticolumn04

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.

comboboxmulticolumn05

Download the Sample Workbook

To see the multi-column combo box, and the code, you can download the Multi-Column Combo Box sample file.

_______________

You may also like...

12 Responses

  1. […] 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 […]

  2. Tarak says:

    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

  3. Jenni says:

    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?

  4. rose morisoli says:

    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?

  5. Lori says:

    How do I get those properties with a form control combo box…trying to use form control combo box instead of activex.

  6. Chris Singleton says:

    Hi Debra,
    I have used your code to make a multi-column drop-down combo box for a reference column on a cashbook in Excel 2007. There are twelve month sheets (Jan, Feb, Mar, etc) in the cashbook and another sheet (sheet named “1” ) which holds the named ranges for the data validation drop down and the 2-column combo box. I used the combo box to give me some selections like: 01 (first column) and loan interest (2nd column), 02 vehicle expenses, 03 … etc. This gives me a number to use in later formulas from the first column which comes out as text but that’s no problem.
    The combo box code worked exactly as intended for the cashbook’s first month’s sheet – but – did you guess, I cannot copy the code to another month and get that month to work as well.
    The code stops on the line:
    Set cboTemp = ws.OLEObjects(“TempCombo”)
    At this point if I go back to the first month it has also stopped working there, too.
    How would this code need to be altered to work on separate sheets all referencing another common sheet for the named ranges?
    I hope you can help
    Kind regards,
    Chris

  7. Justas says:

    I’ ve used code and it’s worked. I would be grateful if you would write code modification of provided example with multi-column combo box with tools in a such way:
    after selecting tool code with tool name from Combo Box I need additionally populate/output tool name to the next cell(column).

    Here is what I need (Lookup value in combo is tool code or tool name, output is the tool code and additionally tool name):

    Tool Code Tool name
    503 Plane
    215 Hammer

    Thank you in advance for your help.

  8. Nathan says:

    Hi,
    I have a lot of excel 2007 workbooks that use the ComboBox activeX and they have worked for years. For some reason this week I can no longer select them with the mouse during normal use to pick from the drop down! I have searched hi and low for a setting in excel that might fix this.

  9. Prahlada says:

    Hi,
    In the above example could you please help me how to increase the length of ToolList .suppose in column A if I have numbers of length 12, and full number has to be displayed in the drop down.
    example:7578458645

    • @Prahlada, in the combo box properties, change the column widths, so you can see the entire number. For example, set it to
      100 pt;100 pt
      Then, change the ListWidth property, to the total of the column widths, e.g. 200 pt
      Next, right-click on the sheet tab, and click View Code. In the Worksheet_BeforeDoubleClick procedure, find the line that sets the width:
      .Width = Target.Width + 15
      Change it to the same width as the ListWidth
      .Width = 200

Leave a Reply

Your email address will not be published. Required fields are marked *