Excel Drop Down lists

M

Marta123

I've created a spreadsheet with a drop down list, however, i want the user
to be able to select an from a list of options, which would then poplulate
the cell with an associated code

If they selected "slate" from the list below, the information put into the
cell would be SL, any idea or advice on how i do this?

Kindest regards

Marta


Slate SL
 
J

JLatham

Begin by selecting that cell and choosing Data Validation again and go to the
[Error Alert] tab and clear the checkbox that says to show an error message
when an entry not in the list is entered into the cell.

Put code similar to this into the worksheet's event code module and edit it
as needed:
Private Sub Worksheet_Change(ByVal Target As Range)
'change this address as required
'this is the address of the cell with
'the list to select from in in and
'for this purpose you must include
'the $ symbol before the column & row
'portions of the address
If Target.Address <> "$D$1" Then
'not the right cell
Exit Sub
End If
Select Case LCase(Target.Value)
'make sure you spell the
'words in the list in
'all lowercase for the
'tests here
Case Is = "slate"
Target = "SL"
Case Is = "steel"
Target = "ST"
Case Is = "silver"
Target = "SI"
'add more Case Is =
'statements as needed
Case Else
'do nothing
End Select

End Sub

To put the code in the right place, select that worksheet and right-click on
its name tab and choose [View Code] from the list. That will open up the
proper code module and you can copy the code above and paste it into the
module, make needed edits and then close the VB editor.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top