Excel Drop Down List Automatically Name To Code

Question

How do I create a drop-down list that will automatically change the name to a code when selected? I am trying to create a drop down list of different facilities; I want the user to be able to view the full name when selecting from the drop down list but I want the selected name to display a code instead of the full name.

Answer 1

'Anything that looks like this is a comment

'Not all are instructions - some are explanations

The address C1:D10 is for the list of values - it can be anywhere on the same sheet, and as large as you need - it just needs to be two columns of values.  If it is on another sheet, then it needs to include the sheet name:

    Target.Value = Application.VLookup(Target.Value, Worksheets("SheetName").Range("C1:D10"), 2, False) 

As written, this code will apply to cells A2:A30 - change that to the range of cells that you want to apply it to:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("A2:A30")) Is Nothing Then Exit Sub   'Addresses of the cells with the dropdown

    'Turn off events to keep out of loops

    Application.EnableEvents = False

     'Change the "C1:D10" in the next line to the address of your list

    Target.Value = Application.VLookup(Target.Value, Range("C1:D10"), 2, False) 

    'Turn events back on to get ready for the next change

    Application.EnableEvents = True

End Sub

Answer 2

Thanks I am going to give it a try, I have 31 cells I would like to apply this to. I haven't done this in a while so thanks for your help; so that I am clear everything next to an apostrophe are instructions for me, they are not apart of the code right? Also for the "C1:D10" portion is this the range of the code I want to appear or the range of the list itself?

Answer 3

Make a 2-column list of your Names and codes, with names in the first column. Then use Data Validation on your entry cell with the list option, using the values from the first column. Then:

1. Copy this code.
2. Right-Click the sheet tab of interest.
3. Select "View Code"
4. Paste the code into the window that appears.
5. Save the file as a macro-enabled .xlsm file.
6. Make changes the the cells addresses as needed - see comments

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address <> "$A$2" Then Exit Sub   'Address of the cell with the dropdown

    'Turn off events to keep out of loops

    Application.EnableEvents = False

     'Change the "C1:D10" in the next line to the address of your list

    Target.Value = Application.VLookup(Target.Value, Range("C1:D10"), 2, False) 

    'Turn events back on to get ready for the next change

    Application.EnableEvents = True

End Sub

If you have multiple cells to apply this to, it is easy to modify, so let me know....