Thanks Dave for your suggestion. I made the changes you suggested and
it worked perfectly the first time. Much appreciated.
regards,
Enzo
On May 25, 1:13*pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> 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 wrote:
>
> > 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- Hide quoted text -
>
> - Show quoted text -
Similar Threads
