Excel Drop Down List Automatically Changes Product Name to Code
Why Stack Overflow recommends it
Stack Overflow users link it when answering how to show a descriptive drop down list but store only a short code or ID in the cell, and they reuse its VBA as a starting point.
Recommended by 1 people across 1 different months, 2012 to 2018.
What it is
A 3.5 minute Excel tutorial by Debra Dalgleish of Contextures. It shows how to build a data validation drop down from an Excel Table column, then use a Worksheet_Change VBA macro to replace the selected product name with its product code.
What people said
Recommends Hadi · 2016-08 · Stack Overflow · on “Accessing Dynamic Named Ranges in VBA”
hi, do you have any issue using tables in excel? that gives you automatically a named dynamic range. pls see this video: [this video] it explains how to use a dynamic range name. hope it helps!
Cites toolshed · 2012-05 · Stack Overflow · on “Show full drop-down list, but only populate field with characters before a "-"?”
I found the answer in a Contextures Video [this video] . There is a link to the code needed on the sheet in a downloadable XLS file. Option Explicit ' Developed by Contextures Inc. ' [LINK] Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo errHandler If Target.Cells.Count > 1 Then GoTo exitHandler If Target.Column = 6 Then If Target.Value = "" Then GoTo exitHandler Application.EnableEvents = False Target.Value = Worksheets("codes").Range("C1").Offset(Application.WorksheetFunction.Match(Target.Value, Worksheets("codes").Range("activitycodes"), 0) - 1, -2) End If exitHandler: Application.EnableEvents = True Exit Sub errHandler: If Err.Number = 13 Or Err.Number = 1004 Then GoTo exitH
Cites Abby · 2018-11 · Stack Overflow · on “How can I use VBA to enable multiple Excel drop down lists that enter a different value from what the user selects?”
This is my first attempt at using VBA. I'm adapting a script offered by Contextures.com that goes with "Excel drop down list automatically changes product name to code" ( [this video] ). I've successfully created on dropdown that adds the ID that is associated with the parameter type that a user selected (e.g. when the user selects "Chlorophyll" from a dropdown on the data entry Excel worksheet, Excel enters the ID number associated with that parameter name in the same cell, based on a list contained in a table on a second worksheet). However, when I try to add more options, the script stops working. The adapted code adds two ElseIf statements for other column dropdowns. When the script runs it d