Prev: rounding problem need accuracy when using V Lookup
Next: how to apply Warning massage
From: Marta123 on 6 May 2010 07:17

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


From: JLatham on 6 May 2010 08:13
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.


"Marta123" wrote:

>
> 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
>
>
 | 
Pages: 1
Prev: rounding problem need accuracy when using V Lookup
Next: how to apply Warning massage