I have a drop-down list (using Data Validation).

Based on which answer is selected from the list, I would like to launch
different macros.

How do I set it up so when you pick "A" from the drop-down it runs Macro_A,
and "B", Macro_B, etc.?

Thanks

Hi,

Assuming, cells in column A has the validation list, in the worksheet code
module, paste this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Target.Value = "a" Then
'call macro a
ElseIf Target.Value = "b" Then
'call macro b
End If
End If
End Sub

This solution has nothing to do with the validation list. Meaning, wether
there's data validation on the cell or not, if you type in "a", the code will
execute.

--
Hope that helps.

Vergel Adriano


"RJB" wrote:

I have a drop-down list (using Data Validation).

Based on which answer is selected from the list, I would like to launch
different macros.

How do I set it up so when you pick "A" from the drop-down it runs Macro_A,
and "B", Macro_B, etc.?

Thanks

Thanks. I'm still having some trouble, so let's test my understanding of this.

My macro is called "Jump".

The cell where I would put the information is A1. If I type in the letter
"a" (no quotes), I would like to automatically run macro "Jump".

"...in the worksheet code module..."
That's the general "Microsoft Visual Basic Editor", right? So I'm typing
below my macro code?

If Target.Column = 1 Then
This is saying, look anywhere in Column A for the input. If I wanted to have
it look at Column B, I would change this to "If Target.Column = 2", Column E
"If Target.Column = 5" and so on.

If Target.Value = "a" Then
Again, if anywhere in Column A I type the letter "a", then it will run the
next line. If I wanted to have it run the macro whenever I typed in the word
"globular", I would change this line of code to "If Target.Value = "globular"
Then".


'call macro a
Here I got stuck.

I typed it exactly as above:
' call macro Jump
and it turned green, like a comment line.

So I typed
Call macro Jump
and get the "Compile error: Expected: end of statement"

So I typed simply
Call Jump
and it let me go.


NOW....
When I go back to my spreadsheet, NOTHING HAPPENS. I have tested my macro -
if I select "Jump" from the macro list,it runs correctly.

But when I type in columnn A, I get nothing.

What am I missing here?

Thanks - it looked like an elegant and simple solution! (And it probably
is... I just can't get it off the ground.)

Hi,

Your understanding of the code is correct. I had put in

'call macro a

as a comment line to tell you that that is where you need to call your
macro. So you did the right thing by calling your macro with Call Jump or
simply

Jump

would also be just fine.

You need to put the code in the Worksheet code module. Right-click on the
sheet tab, then select View Code. It will take you to the worksheet code
module. Paste the code there.


--
Hope that helps.

Vergel Adriano


"RJB" wrote:

Thanks. I'm still having some trouble, so let's test my understanding of this.

My macro is called "Jump".

The cell where I would put the information is A1. If I type in the letter
"a" (no quotes), I would like to automatically run macro "Jump".

"...in the worksheet code module..."
That's the general "Microsoft Visual Basic Editor", right? So I'm typing
below my macro code?

If Target.Column = 1 Then
This is saying, look anywhere in Column A for the input. If I wanted to have
it look at Column B, I would change this to "If Target.Column = 2", Column E
"If Target.Column = 5" and so on.

If Target.Value = "a" Then
Again, if anywhere in Column A I type the letter "a", then it will run the
next line. If I wanted to have it run the macro whenever I typed in the word
"globular", I would change this line of code to "If Target.Value = "globular"
Then".


'call macro a
Here I got stuck.

I typed it exactly as above:
' call macro Jump
and it turned green, like a comment line.

So I typed
Call macro Jump
and get the "Compile error: Expected: end of statement"

So I typed simply
Call Jump
and it let me go.


NOW....
When I go back to my spreadsheet, NOTHING HAPPENS. I have tested my macro -
if I select "Jump" from the macro list,it runs correctly.

But when I type in columnn A, I get nothing.

What am I missing here?

Thanks - it looked like an elegant and simple solution! (And it probably
is... I just can't get it off the ground.)

You need to put the code in the Worksheet code module. Right-click on the
sheet tab, then select View Code. It will take you to the worksheet code
module. Paste the code there.

So I moved the code from VBAProject (Book.xls)/Modules/Module1 to VBAProject
(Book.xls)/Microsoft Excel Objects/Sheet1 (Sheet1)

Now, when I type ANYTHING in the column, the spreadsheet jumps to the Code
page, highlights "Sub Worksheet_Change(ByValTarget As Range) and gives me the
error message:

"Compile error:
Procedure declaration does not match description of event or procedure
having the same name"


OK, what the heck is THAT?

Make sure there's a space between Byval and Target. i.e.,

Sub Worksheet_Change(ByVal Target As Range)


--
Hope that helps.

Vergel Adriano


"RJB" wrote:

You need to put the code in the Worksheet code module. Right-click on the
sheet tab, then select View Code. It will take you to the worksheet code
module. Paste the code there.

So I moved the code from VBAProject (Book.xls)/Modules/Module1 to VBAProject
(Book.xls)/Microsoft Excel Objects/Sheet1 (Sheet1)

Now, when I type ANYTHING in the column, the spreadsheet jumps to the Code
page, highlights "Sub Worksheet_Change(ByValTarget As Range) and gives me the
error message:

"Compile error:
Procedure declaration does not match description of event or procedure
having the same name"


OK, what the heck is THAT?