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:
Dim sArg As String
sArg = "This is a test"
Sheet1.Shapes(1).OnAction = "'ShowAnArgument """ & sArg & """'"
Sub ShowAnArgument(sArg As String)
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.