Excel Data Validation Combo box Codes

Instead of selecting a product code in an Excel drop down list, it’s usually easier to select a product name instead. However, your pricing calculations might use the product code, instead of the product name, so you need that information.

With a bit of programming in Excel, you can show a description in a combo box, but enter the matching code for the selected item into the cell. You’ve seen other versions of my data validation combo box code:

Keep reading, to see how the new combo box codes example works.

Select From a Combo Box

I’ve uploaded a new sample file on the Contextures website, which lets you select a month name, or weekday name, from an Excel combo box.

datavalcomboboxcodes01

When you leave the combo box, the month number, or weekday code is automatically entered in the cell, instead of the full description.

datavalcomboboxcodes02

The Named Ranges

In the data validation cells, lists are allowed, and they refer to the named ranges – DayList and MonthList – where the short codes are stored.

datavalcomboboxcodes03

Another range is created for each list, to include the description column, and these names end with “Codes” – DayListCodes and MonthListCodes.

These “Codes” ranges are used to fill the combo box, which is formatted with 2 columns. The first column has a width of zero, so the codes aren’t visible.

datavalcomboboxcodes04

Download the Sample File

To test the combo box codes example, and see how it works, you can download the sample file from the Contextures website. In the Data Validation section look for DV0057 – Data Validation Combobox Codes. The file is in Excel 2007/2010 format, and contains macros.

______________________

You may also like...

6 Responses

  1. rich soby says:

    don’t seem to find DV57 either here or at your web site http://www.Contextures.com????

    Rich

  2. Julian says:

    Hi,
    These samples are great, however I’ve got an offset formula (used to create the Dependent dropdowns, from http://www.contextures.com/xlDataVal15.html) so when the VBA Code runs to populate the combo box with .ListFillRange = str the str does not evaluate to a named range, and nothing is displayed in the combo box.

    Has anyone come up with a way to combine this excellent method of displaying data in a combo box AND using a formula for the list values, as opposed to a simple named range?

    J

  3. Davo says:

    I’ve been trying to get your Data Validation with Combo Box to work. I am using Of2010 and am working in a .xlsm formatted file. When I look at your example and view properties for each separate cell it shows a separate linked cell value for each. When I create the combo box in my worksheet, how do I get it to “propagate” to all the cells where I have set up the validation rule? Is there some “magic” to what I should put in the Linked Cell value when I create or modify the properties of the Combo Box? I don’t see a way to attach that spreadsheet here!

  4. David says:

    I am trying to copy this functionality into an Excel 2010 workbook, specifically a worksheet that has a dropdown list linked to a named range. The comb box I create will not disappear when I exit design mode. I have copied the VBA code into the corresponding workbook and named the combo box the same as the one in your sample file. What am I missing?

  5. Marc says:

    HI!!! I love the code you wrote and it works great on one sheet but I want to use it on another sheet in the same workbook. I have a different name for the combo box on the second sheet and I changed it to the right one in the code(for that sheet). Now neither of the comboboxes work.
    I need some help please!
    There must be a simple way to run this macron on multiple sheets

    Thanks
    Marc

Leave a Reply to David Cancel reply

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