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.

Show Description, Enter Code

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.

Select From a Combo Box
Select From a Combo Box

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.

Named Ranges on Worksheet
Named Ranges on Worksheet

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

0 thoughts on “Excel Data Validation Combo box Codes”

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

  2. 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!

  3. 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?

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

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.