Passing value from menu item into macro

TomW · May 21st, 2009, 04:14 PM

Maybe someone has done this, I cannot figure it out.

I have a custom menu that is created from a table in a worksheet. This is done and works.

The menu is essentially a list, and I want it to run the same code no matter what item is clicked. The difference is that the item name (or the caption if you will) is passed into the sub.

The subname would be "MapPlanting(Crop as string)".

Crop would be one of the 20 potential items, and the sub could take over from there and find the criteria associated with the selection.

I cannot get it to pass in the variable. When the menu is built I tried the following:

PositionOrMacro = "MapPlanting(" & Chr(34) & MenuArray(3, y) & Chr(34) & ")"

But this yields the error message:

"The macro 'MapPlanting("Cab") cannot be found."

Anyone know how to do this??

Shasur · May 21st, 2009, 09:26 PM

You can try Application.Run. For example:

Code:

 
Sub SubWithArgs(ByVal sMsg)
    MsgBox sMsg
End Sub
 
Sub CallSubWithArgs()
Application.Run "SubWithArgs", "Tom"
End Sub

You can use

Application.Run "MapPlanting" , MenuArray(3, y)

TomW · May 26th, 2009, 08:41 AM

Thanks for your response.

I don't understand what you are telling me. What I keep running into is that the selection of an item from the drop down menu results in a command that doesn't allow me to pass arguments.

If I do your SubWithArgs(ByVal sMsg) I get the same result, ie SubWithArgs("Corn") will still give the macro not found message.

What I ended up doing was having the routine that builds the custom menu build all the appropriate subroutines automatically by inserting code into a module. All these subs call another routine (kind of like you suggest) and pass the arguments to it. The real trick here was that I want the menu to change according to the user, thus the need for dynamic on-the-fly subs.

Would still like to know if what you propose can work, but I wasn't able to.

Thanks again