Access Validation Value on button click

[B Rossio]

How would I be able to see what a validation lists current value is after pressing a programed button. The idea is to have a dropdown list where an item is selected, the user pushes the button and then an action is taken upon that certain value selected in the list. A simple example is having a dropdown list of "Orange, Apple, Mango, Pear, Peach" and a Cell that has the text "This person's favorite fruit is:" and then they select it from the dropdown list and press the button. Let's say they select Apple, the cell would now read "This person's favorite fruit is an Apple"

Sub DropDownList()

Range("A28").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="Orange, Apple, Mango, Pear, Peach"

End Sub

 

[mikerickson]

You could put list validation on a cell with the custom number format "This person's favorite fruit is "@

 

[B Rossio]

The end process is going to be much more complicated than the example I'm using. Basically, I need to see what the user selected from the drop down menu, so that I can use it in multiple cells.

 

[B Rossio]

The end process is going to be much more complicated than the example I'm using. Basically, I need to see what the user selected from the drop down menu, so that I can use it in multiple cells.

Here is a visual representation of a dumbed down version of what I would be trying to do.

 

[mikerickson]

If you put a validation list in a cell, an absolute reference to that cell can be used in many other formulas.

 

[B Rossio]

So, say the list is in cell A2 and the user chooses Apple, If I write a command that prints Range("A2") it will print "Apple"?

 

[B Rossio]

Ok, I see that I does return the value selected when called upon. Thank you. But how do I call upon an action after a button click? I have absolutely no idea how to do that. I want the user to make a dropdown selection, press a button in the excel sheet, and then something happens based on what that user picked from the drop down list. Say they pick Mango, a 2 gets added to a certain cell. Or if they pick Peach, a 5 gets added to a certain cell (after the button click).

 

[mikerickson]

Some code like this (where you've put validation on A2 as in post#6)

Select Case Range("A2").Value 
    Case "Apple"
        ' do something
    Case "Mango"
        ' do something else
    Case "Orange"
        ' anohter thing
    Case Else
        ' fourth thing done
End Select

 

[B Rossio]

This adapted perfectly to my data. Thank you so much!