Excel 2007: Pass parameter to macro assigned to button

[RRinTetons]

I have a proliferation of buttons on a template that all do the exact same thing with a single exception. I've written a version of the macro that has a Select Case statement that generalizes the whole thing very nicely, but I can't figure out how to call the darned thing.

I found suggestions that I should be able to do it by assigning the macro as a quoted string, including the argument, and without parenthesis: "MyMacro 1" should call the macro and pass in 1.

However, I can't find a place to enter that as a button's action! Where do I control what the button calls beyond the 'Assign Macro...' dialog that comes up and only lets me pick a macro name?

Or, am I off track, and this doesn't work at all? It seems like the really must be a way to pass an argument somehow, but maybe not?


-
Richard Ray
Jackson Hole Mountain Resort

 

[PHV]

Perhaps you may test Application.Caller ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[electricpete]

How about you call the macro without any argument, and the first thing the macro does is ask you for the argument (inputbox, userform etc)?

 

[RRinTetons]

And the winner is: "Application.Caller" - gives me the name of the button that called the macro and I can use that in the case statement.

I bow in your general direction. I still don't grok the object model well enough to see that kind of thing and it's clearly the way it's supposed to be done!

-
Richard Ray
Jackson Hole Mountain Resort

 

[electricpete]

whoops, I think I misunderstood.

Your difficult is in having one macro call another macro?

Did you try Call subname (argument1name, argument2name...) ?

(Sorry if I have again completely misunderstood your question)

 

[electricpete]

On third thought, ignore my 2nd post. First post was probably closer to addressing your question if that's what you want. (passing an argument to a macro called from the macro menu)