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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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
1 |
'ShowAnArgument "This is a test"' |
Note that if the argument is numeric, you don’t need the double quotes around it.
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!
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.
I want to pass CommandBarControl in the parameter, and it fails.
——————————–
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.
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.
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.
Baba
Thanks Too. Your Example sorted me a great deal.
Thanks working procedure with paramater
Thank you !
Thanks for this tip — helped me solve a problem that was driving me crazy!
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
Thnx!
Ziado
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}
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).
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…
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.
Interesting note… Excel doesn’t like it if you are passing exclamation points.
Example that works:
.OnAction = “‘ShowParm “”x””‘”
Example that doesn’t:
.OnAction = “‘ShowParm “”!””‘”
Sheet1.Shapes(1).OnAction = “‘” & “ShowAnArgument “”” & sArg & “””‘”
Can be replaced by:
Sheet1.Shapes(1).OnAction = “ShowAnArgument “”” & sArg & “”””
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.
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
How do you clear the OnAction property? Settign it to “” or vbNullString doesn’t end up with thinsg as they were before the OnAction property was doen in the first place.
HELP PLZ!
I am passing a variable string through an .OnAction I get everything to come out ok EXCEPT I have extra quotation marks around the variable!
I expect to see the following title for the Sub Name: MyFile.xls!’Close200002′
But in stead I get: MyFile.xls!’Close”200002?’
I am unable to get rid of the extra quotation marks (which are an invalid Sub title, otherwise I wouldn’t have a problem…)
When I try to remove a set of quotation marks I get: MyFile.xls!’Close” & WOrderNum & “‘
Which is obviously not what I want since it isn’t taking the variable for the file name.
I’m not a VB Pro just a hack, so plz excuse the code. I’m using excel 2007 and this is only a small portion of the code I believe to be applicable.
Dim WOrderNum As String
Range(“P8”).Select
ThisWorkbook.Names.Add Name:=“WOrderNum”, RefersTo:=“=$P$8”, Visible:=True
WOrderNum = Range(“WOrderNum”) ‘ In this case the WOrderNum is 200002 referenced from cell P8
Range(“Q8”).Select ‘ INSERT CHECKBOX SUB FOR CLOSING
ActiveSheet.CheckBoxes.Add(1850, 201, 112, 10).Select
With Selection
.Name = M8
.LinkedCell = “L8”
.Value = xlOff
.Characters.Text = “Check Box to CLOSE WO”
.OnAction = “‘Close”“” & WOrderNum & “”“‘” ‘<=—- This is where problem is believed to exist!
Range(“Q8”).Select
End With
‘Down here later in the code I have the Macro record a new macro
End Sub
This part of the program is adding check boxes for Work Orders, which are then clicked to close the work order. The sub that is executed .OnAction closes the work order. (a very annoying sub to write since I couldn’t find a great way to have a macro write another macro…)
Please tell me if you have any ideas as I feel like I’ve tried all the quotation mark combos that can possibly exist!
thanks.
Your expectation doesn’t have any double quotes, so don’t put any in your code.
Are you creating a separate sub for every checkbox? Surely you can just use one sub for all the checkboxes.
That’s it thanks for the solution!
If I wasn’t a complete hack I’m sure I could have found a way not require a sub for each check box…
But due to time constraints that’s not the way it went down.
thanks for your time!
How can I pass to the .onaction a string indicating a form (included in my main.xls project) to be opened ?
Thanks
For Ziado:
Selection.OnAction = “‘action_macro “”” & var1 & “””,“”” & var2 & “””‘”
will work. You forgot the comma…
I am trying to pass multiple parameters to onAction. The 1st parameter is the loop counter (i) and the second is a boolean value.
what would the proper syntax be?
Im getting an error at selection.OnAction and object doest support this property or method.
I am trying to create a button that sends the workbook to an email address.
For multiple arguments the following works for me.
The whole arhument wrapped with single quotes, each argument surrounded by double quotes and separated by commas when more than one argument is used
.OnAction = “‘MacroName””” & argOne & “””, “”” & argTwo & “””, “”” & argThree & “””” & “‘”