problem with OnAction including an unwanted file reference

K

KB

Hi all,

I have one workbook that manipulates other workbooks. Part of this is to
have the controlling workbook insert a button and code behind the button to
other workbooks. As part of the process an external .bas file is read into
the workbooks - that part works OK.

When all processing is finished, the buttons are placed as expected, but the
onAction is giving a reference to the controlling workbook. This should not
be. The code itself is in the manipulated workbooks, not the controlling
workbook. Here is some code:

'add button to Allocation_OUT sheet
Workbooks(data_book.Name).Activate
data_book.Worksheets("Detail_Allocation_OUT").Activate
ActiveSheet.Unprotect Password:="xxx"
Application.CommandBars("Forms").Visible = True

Set btn = ActiveSheet.Buttons.Add(540.75, 12, 141.75, 16.5)
Selection.OnAction = "make_pivot_Out"

The code creates a button on the Detail_Allocation_OUT sheet. But the
reference of the button is:

'Budget Workbook Update.xls'!make_pivot_Out
Budget Workbook Update is the controlling workbook. The reference should
just say "make_pivot_OUT"

Needless to say when the button is clicked it cannot find the macro - it
does not exist in the Budget Workbook Update file.

I don't know why this line:
Selection.OnAction = "make_pivot_Out"
is includng a file reference, and the wrong one at that.

Any ideas how to correct this?

Thanks!

KB
 
Top