VBA list

[cmccabe]

<!-- / icon and title --><!-- message -->
I have a few cells on a spreadsheet that are lists. Using data validation those cells appear as a list. Is there a way using VBA that that list can be referenced? So if I have a saved list called Name, is there a way the list can be referenced when the user is prompted. Instead of entering text, a pulldown list (Name) results. Thanks.

 

[cstimart]

Like this?

Sub List_Macro()
Range("A1").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=$D$1:$D$4"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

You can put your actual list in the red part with the choices separated by commas.

 

[cmccabe]

Is it possible that the above code for the list could be integrated to the code below? Thanks.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("A3:A53")) Is Nothing Then Exit Sub
Cancel = True
Target.Value = InputBox("Enter Case Number Between 1 and 50", "You must Enter a Value")
Do While Target.Value < 1 Or Target.Value > 50
    Target.Value = InputBox("Enter a number Between 1 and 50", "Input Out of Range!")
Loop
Target.Offset(, 4).Value = InputBox("Enter Age", "You must Enter a Value")
Do While Target.Offset(, 4).Value < 1 Or Target.Offset(, 4).Value > 150
    Target.Offset(, 4).Value = InputBox("Enter a number Between 1 and 150", "Input Out of Range!")
Loop
Target.Offset(, 12).Value = InputBox("Enter Donor", "You must Enter Text")
Do While IsNumeric(Target.Offset(, 12).Value) Or Target.Offset(, 12).Value = ""
    Target.Offset(, 12).Value = InputBox("Enter Donor", "Error!")
Loop
Target.Offset(, 14).Value = InputBox("Enter Description", "You must Enter Text")
Do While IsNumeric(Target.Offset(, 14).Value) Or Target.Offset(, 14).Value = ""