Drop Down List and macros

[haas786]

Hi all!

I was wondering if I can use a drop down list to run a macro. For
example, Cell A5 has a drop down box I created using Data...Validation
from the menu bar in Excel and selected "Allow List" to create a drop
down list of 3 Values: "Yes","No","Maybe" - now, here's what I'd like
to do. If I ever select "No", I want a macro to to be run. If I select
"Maybe", I'd like a different macro to be run. Is this possible?

Thanks!

Haas

 

[Corey]

pretty crude but will do it:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Range("A5").Value
If Range("A5").Value = "no" Then
Call Macro4
Else
If Range("A5").Value = "yes" Then
Call Macro5
Else
If Range("A5").Value = "maybe" Then
Call Macro6
Else
If Range("A5").Value = "" Then
Exit Sub
End If
End If
End If
End If
End With
End Sub


Corey....

 

[Otto Moehrbach]

Haas
This macro will do that. Note that nothing will happen if you select "Yes"
in A5. Note that this macro is a sheet macro and must be placed in the
sheet module of your sheet. To access that module, right-click on the sheet
tab, select View Code, and paste this macro into that module. "X" out of
the module to return to your sheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("A5")) Is Nothing Then
If Target.Value = "No" Then Call ThisMacro
If Target.Value = "Maybe" Then Call ThatMacro
End If
End Sub

 

[haas786]

Thanks for all your help. Both methods worked. Gotta say, you guys know
your stuff! Much thanks!

Haas