Data Validation help

[redspanna]

Hi all

Cell F5 in my Data sheet holds a drop down populated by range AA9:AA13 (Data validation - list )

When a selection is chosen from this menu I want a certain 'event' to happen

so AA9:AA13 holds

Car
Bus
Train
Plane
Bike


so if Bike is chosen then run macro "bike"
if plane is chosen run macro "plane" ...etc

any ideas how to get this to happen??

many thanks

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "F5" Then Application.Run Target.Value
End Sub

 

[redspanna]

Hi VoG

But how does your example know which of my macros to run depending on which is selected from the data validation list?

 

[VoG]

Because it uses Target.Value - the value that you selected.

 

[redspanna]

VoG

I'm sorry I still don't understand.....

I have right clicked the sheet tab, selected View Code and pasted in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "F5" Then Application.Run Target.Value
End Sub

When I click on cell F5 I have a chose from the drop down menu..

Dog
Cat
Plane
Car
Boat

if I select Dog from this I want the "Dog" macro to run etc etc

but using ur code above nothing happens when I chose from the validation box in F5..

Sorry

 

[VoG]

I just tested it and it works fine.

Make sure your Dog, Cat etc. subs are in a regular module. Make sure that events are enabled. Press CTRL+G, type in

Application.EnableEvents=True

and press Enter.

 

[redspanna]

getting there....

In module2 I have

Sub Dog()
MsgBox "ruff"
End Sub
Sub Cat()
MsgBox "Meow"
End Sub
Sub Plane()
MsgBox "Woooosh"
End Sub
Sub Car()
MsgBox "Vrooom"
End Sub
Sub Boat()
MsgBox "Whoop Whoop"
End Sub

I have also enabled the events and now

Application.EnableEvents=True

is sitting in the immediate box

When I go back to my sheet and select any of the drop menu all works fine apart from the Dog..I get an error saying Dog Macro can't be found??

 

[VoG]

Make sure that in your data validation list it is actually "Dog" not " Dog" or "Dog ".

You can close the Immediate window.