VBA - Having trouble using .OnAction Programatically

[swayp11]

I am inserting shapes into a sheet and I want an OnAction on each shape with a parameter (which is a variable) to call another sub.

The code runs fine and inserts the OnAction:

With tempShape
  .OnAction = "'PERSONAL.XLSB!MyMacro " & myVariable & "'"
End With

The code for the sub in my personal workbook:

Public Sub MyMacro(VariableName As String)
  'code here
End Sub

When I try and click one of the shapes though, I get:

Cannot run the macro "PERSONAL.XLSB!MyMacro VariableString". The macro may not be available in this workbook or all macros may be disabled.


Anybody know whats going on here?

Thanks!

 

[John_w]

You have to pass the OnAction parameter in a specific way, depending on whether it is string or numeric data. See http://www.mrexcel.com/forum/excel-questions/53220-calling-function-passing-arguments-onaction.html#post245961.

 

[swayp11]

Thanks for your reply. I tried multiple ways to pass it, but it seems as if it doesn't like the Personal Workbook. I think it needs to be in another workbook and be called locally.