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:
- Double-click on data validation cell to open combo box
- Click on data validation cell to open combo box
- Use named lists with data validation combo box
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.
When you leave the combo box, the month number, or weekday code is automatically entered in the cell, instead of the full description.
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.
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.
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.
______________________
don't seem to find DV57 either here or at your web site http://www.Contextures.com????
Rich
Thanks Rich -- the link should work now: http://www.contextures.com/excelfiles.html#DV0057
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
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!
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?