Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help with a Menu Item .OnAction property
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, _ Befo=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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help with a Menu Item .OnAction property
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. ) -- Regards, Tom Ogilvy " wrote in message ups.com... 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, _ Befo=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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help with a Menu Item .OnAction property
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help with a Menu Item .OnAction property
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)) -- Regards, Tom Ogilvy " wrote in message ups.com... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help with a Menu Item .OnAction property
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help with a Menu Item .OnAction property
My mistake, the qualifier for ActionControl is Commandbars
Sub Button1() i = CLng(CommandBars.ActionControl.Parameter) MsgBox i End Sub -- Regards, Tom Ogilvy " wrote in message oups.com... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
OnAction menu item property coding | Excel Programming | |||
Button Selection OnAction property | Excel Programming | |||
Setting OnAction of custom menu item? | Excel Programming | |||
Find OnAction property | Excel Programming |