Dropdown Menus (Display two columns, store only one)

[psycoperl]

Good Morning,

I hope that this finds you all well.

I am working on a project where I want to create a drop down in the cells so that users only can select one value from a pre-defined list. What I would like to do if possible is since the value that will be stored is a code, in the menu I want to have a description also shown. for example the value that would be shown is "LEC", I would like to also have "Lecture" shown next to it in the menu only.

How can this be done?

SCFE_Prep1.xlsx
	C	D
1	Component Codes	Component Descr
2	DTN	Dissertation
3	IND	Independent Study
4	INT	Internship
5	LAB	Laboratory
6	LEC	Lecture
7	MSG	Message
8	REC	Recitation
9	SEM	Seminar
10	SUP	Supplemental Instruction
11	THE	Thesis Research
12	TUT	Tutorial
Preset Values

 

[mumps]

According to your post, the Component Codes and Component Descr are in columns C and D. Will you be using this data as a source for your drop down list? Will the Component Codes always be 3 characters? In which range do you want to put the drop down list?

 

[psycoperl]

HI @mumps

The Component Codes and Description are in the "Preset Values" worksheet. I have a "Course List" worksheet where in column "H" I would want these to popup.

Since the Component Codes are determined by a different office, I just always match my lists to their lists. While I doubt that it would not always be a 3 char field, I cannot guarantee that it would always be a 3 char code... in the future they may decide to make it a longer code.

 

[mumps]

Copy and paste this code into the worksheet code module. Do the following: right click the tab name for your "Course List" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Select any cell in the range H2:H50 (change this range in the code to suit your needs). Make a selection in the drop down list. A message box will pop up with Component Code. The Component Code is stored in the variable "selectedVal".

Dim selectedVal As String
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("H:H")) Is Nothing Then Exit Sub
    selectedVal = Target
    MsgBox Left(selectedVal, WorksheetFunction.Find("-", selectedVal) - 1)
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("H:H")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim Rng As Range, val As String, presets As Worksheet
    Set presets = Sheets("Preset Values")
    For Each Rng In presets.Range("C2", presets.Range("C" & Rows.Count).End(xlUp))
        If val = "" Then val = Rng & "-" & Rng.Offset(, 1) Else val = val & "," & Rng & "-" & Rng.Offset(, 1)
    Next Rng
    With Range("H2:H50").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Application.Transpose(val)
    End With
    val = ""
    Application.ScreenUpdating = True
End Sub

 

[psycoperl]

Would this technique also work via Excel on the Web?

 

[mumps]

To be honest, I don't use web-based software so I guess the only way to find out is to give it a try.

 

[psycoperl]

While I dont use it on the norm.... since we have moved to Remote ops, others in our org that I am sharing this with are probably going to be using the web versions.

Thanks.

 

[Fluff]

The online version of Excel does not support VBA, so you would not be able to use mumps' suggestion.

 

[psycoperl]

The online version of Excel does not support VBA, so you would not be able to use mumps' suggestion.

Thats what I thought...especially with my experience with my other active post of trying to get Excel 365 Web to always open to a specific worksheet in a file.