Need Help with a Menu Item .OnAction property

[marston.gould]

I have added a new menu to Excel called "Budget"

Within the worksheet that this menu will act on are 5 named ranges
whose names are stored in the string RngName, e.g. the first named
range is in RngName(1), the 2nd in RngName(2), etc.

There is a corresponding menu item in the menu to each of these ranges
and depending on which is selected, the same set of actions will occur
to that range.

When I'm setting up the menu can I do the following (I don't have
WIndows vs of Excel to check this at home)

Set HelpMenu = CommandBars(1).FindControl(Id:=30010)
If HelpMenu = Nothing Then
Set NewMenu = Commandbars(1).Controls.AddType:=msoControlPopup,
Temporary:=True)
Else
Set NewMenu = Commandbars(1).Controlls.AddType:=msoControlPopup, _
Before:=HelpMenu, Temporary:=True)
End If
NewMenu.Caption = "&Budget"
For i = 1 to 5
Set MenuItem = NewMenu.Controls.Add(Type:=msoControlButton)
With MenuItem
..Caption = MIStr(i)
..OnAction = Macro1(RngName(i))
If Action(i) = False Then
..Enabled = False
Else
..Enabled = True
End If
End With

I guess my question is, will the .OnAction properly pass the the
RngName(i) string that holds
the range name?

 

[Tom Ogilvy]

No. Not the way you have written it.

The way to do this is to set a string in the parameter property of each menu
item you create, then assign all buttons to the same macro. In that macro
you can use something like

sStr = Application.ActionControl.Parameter
set rng = Range(sStr)

to sort out what action to take. (if you set the range name to be the value
of parameter. )

 

[marston.gould]

Thanks for the response Tom.
However, I'm going to have to plead ignorant.

I'm not familiar with the parameter property.

Would it be something like:

For i = 1 to 5
With MenuItem
..Parameter = RngName(i)
..OnAction = Macro1
End With

I guess I don't have any idea how this works.....

Initially, I had 5 separate macros that were all identical
except that they each worked on a different range, so it seemed kind of
silly to have five identical routines.

I get the part about having to pick the range with set rng = range(str)
or in my case range(rngname(i)), I just didn't know how to pass the "i"
along through the .onaction. I thought that if I
had the macro1 set up as:

Sub Macro1(rngname as String) that that would make sense.
Perhaps I just declare the rngname as a public variable and then
do something like:

Sub Macro1
Call Macro2(Rngname(i))
End Sub

 

[Tom Ogilvy]

if you want to pass in the index then

For i = 1 to 5
With MenuItem
.Parameter = cstr(i)
.OnAction = Macro1
End With


then in the single macro assigned to OnAction

Sub Macro1()
i = clng(Application.ActionControl.Parameter)
Call Macro2(Rngname(i))

 

[marston.gould]

Tom - When I do this, I'm getting:

Run-time error '438':

Object doesn't support this property or method
on the
i= clng(Application.ActionControl.Parameter) statement

any thoughts?

 

[Tom Ogilvy]

My mistake, the qualifier for ActionControl is Commandbars

Sub Button1()
i = CLng(CommandBars.ActionControl.Parameter)
MsgBox i
End Sub