Launching Macro from Validation List

R

RJB

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
 
V

Vergel Adriano

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.
 
R

RJB

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..."
Click to expand...
That's the general "Microsoft Visual Basic Editor", right? So I'm typing
below my macro code?
If Target.Column = 1 Then
Click to expand...
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
Click to expand...
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
Click to expand...
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.)
 
V

Vergel Adriano

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.
 
R

RJB

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.
Click to expand...

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?
 
V

Vergel Adriano

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

Sub Worksheet_Change(ByVal Target As Range)
 
R

RJB

This worked like a charm, but now I find myself totally changing the design
of what I'm doing... and may not need this code!

So now that I know have to do it, I will have to figure out where to use it.

Thank you,
 
R

RJB

OK, now all of a sudden, it's NOT working. I don't know what I changed.

==========================
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$d$8" Then
If Target.Value = "2" Then
Application.Goto reference:="N", Scroll:=True
ElseIf Target.Value = "1" Then
Application.Goto reference:="Answer_A", Scroll:=True
End If
End If



End Sub

================

I have two cell ranges - "N" and "Answer_A".
If the answer to the question is "Y", I want to go to "Answer_A". Otherwise,
"N".

When I type in the cells, NOTHING happens.
 
R

RJB

Ahhh... "$D$8" works, "$d$8" does NOT.

FINAL Question - I hope.

I changed my mind about using drop down data validation boxes, and want to
use Form Toolbox Option buttons instead... So if you click "Yes", you go to
one cell reference, and "No" the other.

I mapped the option button to a cell; and wrote the code so if the cell is
"1" ("Yes") it moves, and if "2" to the other. But even though the cell value
changes, it doesn't trigger the code.

What's up with that?
 
O

olimiller

Hi RJB and Vergel Adriano,

I have followed all of the steps in your instructions and I still can’t get
my Macro to launch from my validation list or by typing my macro shortcut key
letter into Column A.

Please could you e-mail me an example spreadsheet so I can look into it and
workout why mine isn’t working?

Many thanks for any help

Oliver
([email protected])
 
O

olimiller

Hi,

No worries - No worries – I’ve cracked it!

I just needed to enter the code like:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Target.Value = "a" Then
a
ElseIf Target.Value = "b" Then
b
End If
End If
End Sub
 
Top