How To Access A Drop-Down List Selection in Excel

[richlocus]

Hello:

From my research, a drop-down list allows a user to select ONE item from a drop-down list of values. The articles I reviewed so far did not provide a solution for VBA. I want to create this drop-down list on a standard worksheet (not a form). I envision the user clicking a down-arrow and a list of values is displayed. The user selects one of those values and it appears in the cell where the drop-down values are stored.

Next, I need to access the value the user selected using VBA (which would probably access the value in the cell???)

Could you point me to some examples or explanation?

Thanks,
Rich Locus

[HansV]

See Make Excel Drop Down List in Worksheet Cell.

The list item selected by the user becomes the value of the cell, so you simply get the call value in VBA.

A simple example of creating a data validation drop-down in a range of cells using VBA code:

Code: Select all

Sub CreateDataValidationDropDown()
    With Range("A1:A5").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=$N$2:$N$9"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = "Drop-down List"
        .InputMessage = "Please select an item from the list."
        .ShowInput = True
        .ErrorTitle = "Invalid Input"
        .ErrorMessage = "Only items from the list are allowed!"
        .ShowError = True
    End With
End Sub

[richlocus]

Thanks Hans!