Assigning macro buttons to macro code in another file

[Enz]

Hello

Does anyone know if you can do the following?

I coded a macro that creates several tabs within it (call it
Executive), each of which contains a button to invoke a macro (and
each sheet has its' own macro code). Then I create an output file
that copies the desired tabs from Executive to a new Workbook output
file. I am currently coding the portion of the Executive macro where
I am re-assigning the existing button in each tab to point to the
macro code within the output file sheets, that I am attempting to
create. The output file will be run independently from the
originating Executive macro file, so I need the output file to
reference only macro code within itself.

When I attempt to specify the output file name when assigning the
button to execute the macro code within the new Workbook output macro
file, I get a run time error '1004' 'Unable to set the OnAction
property of the Button Class'. I believe I am getting this message
because the button class does not allow you to specify another file
reference when assigning macro code to buttons outside the current
macro executing. Can anyone confirm my belief? Or perhaps I have an
error I am not yet seeing.

This is the logic that attempts to re-assign the macro code to
reference the logic in the output file

'Note that this index is referencing the Executive workbook but will
be the same number in the output workbook
'Note "Desk Output" is the new workbook that contains the subroutine
CopyLogs in each worksheet tab

lcurrentDate = Format(Date, " yyyy mmm d")
lindex = ThisWorkbook.Sheets(ActiveSheet.Name).Index
ActiveSheet.Shapes("Button 3").Select
Selection.OnAction = "Desk Output " & lcurrentDate & "!Sheet" & lindex
& ".CopyLogs"

I have validated all the variables and I believe I have the correct
values as it stands if this were to work.

Any thoughts would be greatly appreciated.

Regards,
Enz

 

[Dave Peterson]

How about an alternative that will make life lots easier?

Instead of using buttons from the forms toolbar and assigning macros to them,
how about using commandbuttons from the Control Toolbox toolbar.

Since your code is in the sheet module anyway, there wouldn't be much of a
change (since the code will travel with the copied worksheet when it's placed
into the new workbook).

But it will require some changes.

 

[Enz]

Thanks Dave for your suggestion. I made the changes you suggested and
it worked perfectly the first time. Much appreciated.

regards,
Enzo