Passing Arguments Through OnAction
The OnAction property can be used to assign a macro to various objects, such as a Shape, a CommandBarControl, or a CommandButton (from the Forms toolbar). If you need to assign a macro that takes an argument to one of those objects, you can do it - you just have to get the single and double quotes right. Here’s an example:
Sub SetButton()
Dim sArg As String
sArg = “This is a test”
Sheet1.Shapes(1).OnAction = “‘” & “ShowAnArgument “”" & sArg & “”"‘”
End Sub
Sub ShowAnArgument(sArg As String)
MsgBox sArg
End Sub
This assumes that there is a Forms toolbar CommandButton on Sheet1 and that it is the first Shape in the Shapes collection. Clicking the CommandButton after running SetButton should produce the argument passed to it. The string that is assigned to OnAction looks like this
‘ShowAnArgument “This is a test”‘
Note that if the argument is numeric, you don’t need the double quotes around it.
Nick Burns:
I’ve been following your blog since it’s inception.
This routine has made me revisit my CommandBar routines for setting up toolbars/menus.
I used to just use the unused properties of the commandbarbutton such as Tag, Parameter, DescriptionText2, and even the HelpFile and HelpContextID to hold arguments for the Macro assigned to the OnAction property.
The Macro would read the ActionControl to determine which button was pushed and then read the various properties to determine it’s course of action.
This routine will let me easily pass those arguments correctly.
Thanks!
3 June 2004, 9:45 amMike:
Can’t get it to work for some reason. I don’t get errors, but (using the exact code from the example) the ShowAnArgument code does not run. Strange.
4 August 2004, 2:41 pmBaba:
I want to pass CommandBarControl in the parameter, and it fails.
2 March 2005, 8:55 am————————————————
Set cbSubMenu = LocalMenu.Controls.Add(msoControlPopup, 1, , , True)
With cbSubMenu
.Caption = ItemDetail(i, 2)
.Tag = ItemDetail(i, 2)
.BeginGroup = True
‘ .OnAction = ThisWorkbook.Name & “!CreateMenuItems(”"” & cbSubMenu & “”", “”" & Category & “”", “”" & ChildDimension & “”", “”" & ChildNum & “”", “”" & ItemDetail(i, 2) & “”")”
‘ End With
————————————————
Could you please tell me how to get around with this problem.
Jon Peltier:
In OnAction, you can only pass text. cbSubMenu is an object variable (specifically a commandbar control), so you need to pass a text representation of it, like cbSubMenu.Caption. And on the receiving end, the CreateMenuItems procedure must be able to accept the caption, and know where to find it, because there’s no cbSubMenu.Caption.Parent to find where it’s located.
3 March 2005, 6:16 amBaba:
I need to change the visible property of a PivotItem in Pivot table in a sub routine in VBA Excel.
This sub routine I am calling from the OnAction method of a Menu Popup Control. The visible property seems to be not working when I call this way.
Please help.
24 March 2005, 9:41 amBaba
Mathea:
Thanks Too. Your Example sorted me a great deal.
19 September 2005, 8:38 amsandip:
Thanks working procedure with paramater
27 October 2005, 2:17 amJC:
Thank you !
1 November 2005, 11:22 amSydneyGeek:
Thanks for this tip — helped me solve a problem that was driving me crazy!
10 January 2006, 6:20 amZiado:
I am struggling to pass more than one variable to a macro in excel using OnAction. I’ve tried so many different formats but to no avail. Any help would be greatly appreicated… It works very well when I am passing one variable, but with more than one it never works. Assume the macro to be triggered is action_macro, and the 2 variables are var1 and var2
I tried all of the following syntax’s
Selection.OnAction = “‘action_macro “”" & var1 & “”" var2 & “”" ‘”
Selection.OnAction = “‘action_macro “”" & var1 & “” & var2 & “”" ‘”
Selection.OnAction = “‘action_macro “”" & var1 & var2 & “”" ‘”
Selection.OnAction = “‘action_macro “”" & var1 & var2 & ” ‘”
Selection.OnAction = “‘action_macro “”" & var1 & “”" “”" & var2 & “”"‘”
I even tried defining a variable x as any of the following and nothing worked
x = var1 & ” ” & var2
x = var1 & “,” & var2
x = “(” & var1 & “, ” & var2 & “)”
Selection.OnAction = “’sort_sales_override_for_buttons1 “”" & x & “”" ‘”
Sub action_macro(var1, var2)
end sub
Your help would be greatly appreciated
14 April 2006, 8:24 amThnx!
Ziado
Tushar Mehta:
For a more extensive treatment of this subject see
Using procedures with arguments in non-obvious instances:
macros associated with shapes, forms, and commandbar elements, and called by the OnTime and OnKey methods
http://www.tushar-mehta.com/excel/vba/xl%20objects%20and%20procedures%20with%20arguments.htm
And, feel free to suggest a better (crisper, shorter) title for the chapter. {grin}
14 April 2006, 3:26 pmRaphael:
As a quick note, if anybody gets an error msg “Could not set onAction property for …”, it may be because you’ve used the wrong single quotes. I ran into the same problem. For some reason when the single quotes provided in the code above render as the wrong quote/accent (the one on the same key as the tilda instead of the regular single quote).
15 June 2007, 6:45 amMcfly:
In Excel 2007 I was unable to get the above to work. A few things I found were necessary to get it to work were:
1) Ensure the “macro” was located in a module. Yes for some reason it just wouldn’t work otherwise…
2) Specify the full macro name (see example below).
In my case I have a column of buttons. Each button is the same dimension as a single cell in the column. I needed to pass the current row to the macro, where the current row is where the button is located). The row number is a string & the macro converts it to an integer. The following code was used to point to my macro…
.OnAction = “‘” & “Module1.MacroName “”" & row & “”"‘”
May help someone…
15 September 2007, 2:05 amTushar Mehta:
Mcfly: I don’t understand what you mean by the macro must be located in a module since every sub/function must be in some kind of a module.
Also, in a test with 2007 a few minutes ago, I did not have to specify the module name as you apparently had to. Typically, that is required only when one has routines with the same name in different modules. And, that need to uniquely specify which routine one means has always been the case.
18 September 2007, 6:05 amTim:
Interesting note… Excel doesn’t like it if you are passing exclamation points.
Example that works:
.OnAction = “‘ShowParm “”x”"‘”
Example that doesn’t:
29 March 2008, 11:31 pm.OnAction = “‘ShowParm “”!”"‘”
Amolin:
Sheet1.Shapes(1).OnAction = “‘” & “ShowAnArgument “”” & sArg & “””‘”
Can be replaced by:
Sheet1.Shapes(1).OnAction = “ShowAnArgument “”" & sArg & “”"”
31 March 2008, 6:58 pmRine:
Help Pls!
I got the following msg when I tried running the second command button that is created together with the new sheet when i clicked on the first command button.
"The macro 'main.xls!DisplayMessage' cannot be found
I have extracted the following code:
Sheets.Add
ActiveSheet.Buttons.Add(144, 38.25, 57, 21).Select
Selection.OnAction = "DisplayMessage"
ActiveSheet.Shapes("Button 1").Select
Selection.Characters.Text = "Display Msg"
With Selection.Characters(Start:=1, Length:=11).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveSheet.Range("A1").Select
End Sub
I really wish to know what is the error that stoping it from executing the code.
23 July 2008, 3:20 amRine:
Sry, made a mistake at the button name. :p
Sheets.Add
ActiveSheet.Buttons.Add(144, 38.25, 57, 21).Select
Selection.OnAction = "DisplayMessage"
ActiveSheet.Shapes("Button 1").Select
Selection.Characters.Text = "Display Msg"
With Selection.Characters(Start:=1, Length:=11).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveSheet.Range("A1").Select
End Sub