Assign Macro to Shape via VBA (with params)

[jaredc]

I'm trying to assign a macro to a shape via another macro. I need to put three parameters in there that are variables in my original script. I'm getting a bit lost on the syntax and where I need my quotes.

The end result I want is the shape to be assigned with 'myMacro "var1", "var2", var3"'

I've selected the shape and used:

Selection.OnAction = "'myMacro "" & var1 & ", "" & var2 & ","" & var3 & ""'"

But, the variables aren't being set correctly. I'm sure the issue is with my quotes and such. Anyone have any good advice?

 

[Legacy 456155]

What are the types of var1,2,3?

If all of your args are strings:

Selection.OnAction = "'myMacro """ & var1 & """, """ & var2 & """,""" & var3 & """'"

This is easier IMO. A reusable function is even better.

Sub Example()
    Dim var1 As String, var2 As Date, var3 As Integer, ret As String
    
    'if sub is in worksheet with codename "Sheet1"
    'ret = "'Sheet1.myMacro {0}, {1}, {2}'"
    'if sub is in workbook with codename "ThisWorkbook"
    'ret = "'ThisWorkbook.myMacro {0}, {1}, {2}'"
    'in module
    ret = "'myMacro {0}, {1}, {2}'"
    
    ret = Replace(ret, "{0}", """" & var1 & """")
    ret = Replace(ret, "{1}", "#" & var2 & "#")
    ret = Replace(ret, "{2}", var3)
    
    Selection.OnAction = ret
End Sub

Sub myMacro(argText As String, argDate As Date, argNumber As Integer)

End Sub

 

[John_w]

It depends on the data types of the variables. String variables require double quotes around them and numeric variables (Long, Double, Date, etc) don't. I also find it easier to call a function to put quotes around the string values. For example:

Sub Create_OnAction()
    Dim var1 As String, var2 As Long, var3 As Date
    var1 = "xxx"
    var2 = 123
    var3 = Date
    With Sheets("Sheet1").Shapes("Edit_Button")
        .OnAction = "'myMacro " & Q(var1) & "," & var2 & "," & CLng(var3) & "'"
    End With
End Sub

Private Function Q(text As String) As String
    Q = Chr(34) & text & Chr(34)
End Function

Sub myMacro(var1 As String, var2 As Long, var3 As Date)
    MsgBox "var1 = " & var1 & vbCrLf & _
           "var2 = " & var2 & vbCrLf & _
           "var3 = " & var3
End Sub

 

[jaredc]

Should have been more specific. All variables are strings. So, lots and lots of quotes.