Dropdown Menus (Display two columns, store only one)

psycoperl

Active Member
Joined
Oct 23, 2007
Messages
338
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
  3. Web
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
CD
1Component CodesComponent Descr
2DTNDissertation
3INDIndependent Study
4INTInternship
5LABLaboratory
6LECLecture
7MSGMessage
8RECRecitation
9SEMSeminar
10SUPSupplemental Instruction
11THEThesis Research
12TUTTutorial
Preset Values
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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?
 
Upvote 0
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.
 
Upvote 0
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".
VBA Code:
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
 
Upvote 0
Would this technique also work via Excel on the Web?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
The online version of Excel does not support VBA, so you would not be able to use mumps' suggestion.
 
Upvote 0
Solution
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top