drop down menu

  • Thread starter Thread starter Gator Girl
  • Start date Start date
G

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.
 
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.
 
Back
Top