drop down menu

[Gator Girl]

I want to put a drop down menu (the same menu) in 10 different rows in the
same column. I want the list to say, for example 990 - LWOP. But when
they select, I only want the 990 to be entered in the cell.

Suggestions, please, and thank you.

 

[Gary''s Student]

The following example uses DataValidation and an event macro.

Put the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Value = Left(Target.Value, 3)
Application.EnableEvents = True
End Sub

Then enter and run:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 11/30/2009 by James Ravenswood
'

'
Range("A1").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$J$1:$J$3"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = False
End With
Range("J1").Value = "100 - stuff"
Range("J2").Value = "200 - more stuff"
Range("J3").Value = "300 - excess stuff"
End Sub

Macro1 sets up a very simple data validation in cell A1. The event macro
simply keps only the first three characters from what the user selects.