Selection.OnAction = “Workbookname!Macroname”

Question

Say you have two Workbooks one called “MyWorkbook” and the other called “PatchMyWorkbook”. Both workbooks are open at the save time. The “PatchMyWorkbook” has a macro to add a button and assign an existing macro of “MyWorkbook” to “MyWorkbook” The existing macro in “MyWorkbook” is called “PrintPage”

Windows(“MyWorkbook”).Activate  
Sheets("Sheet1").Activate
ActiveSheet.Buttons.Add(665.25, 43.5, 89.25, 45).Select
Selection.OnAction = "PrintPage"

This does not cause an error while the “PatchMyWorkbook” code executes but the newly added buttons macro will point to “’PatchMyWorkbook’!PrintPage” rather than just “PrintPage” of the “MyWorkbook”

Question: How can you set the “OnAction” for a macro button across workbooks so that the macro will point to the current workbook not the workbook from where the macro has been created?

Answer 1

In my opinion .OnAction property should be set in this way:

Selection.OnAction = myWbk.Name & "!PrintPage"

By the way, the idea from your comment (changed a bit below):

Selection.OnAction = "'" & myWbk.Name & "'" & "!" & "PrintPage"

is working for me as well (Excel 2010).

Answer 2

You need to include the name of the sheet or module where PrintPage is defined.

Dim methodName As String
With <module or sheet where 'PrintPage' is defined>
    methodName = "'" & MyWbk.Name & "'!" & .CodeName & ".PrintPage"
End With
MyWbk.Sheets("Sheet1").Shapes("ButtonName").OnAction = methodName

The single quotes surrounding MyWbk.Name are important.

Answer 3

A quick and easy way is :

workbooks("name_of_workbook").Worksheets("name_of_sheet").Shapes("name_of_button").OnAction = "name_of_your_macro"

Just substitute the different "name_of_..." by your names.

Does it work?