HELP! Macro FAILS when I copy the worksheet...

I have a macro (does simple formatting) that I need to leave in the worksheet
object (NOT in a module). Reason for this is that I'm anticipating users
needing to copy the worksheet into another file/new file. I need the macro
to continue to work within ANY copied worksheet, whether in the same original
file or in a separate file, INDEPENDENT of the original worksheet/file or any
other worksheet. The user will CLICK a BUTTON on the worksheet to run the
macro.

PROBLEM: though the macro is carried with the worksheet (which is good),
the macro won't execute correctly, or at all (ie, the BUTTON no longer
works). When the BUTTON is clicked, the program either tries to open the
original file where the macro was built, or simply bombs out (error code of
400).

How do I ensure that when I click a "button" on the worksheet that the macro
assigned to it will ALWAYS work no matter where/how many times the worksheet
is copied?

THANKS FOR ANY HELP!!!


Chris M

It sounds like you are using a button from the Forms Toolbar. Use a
commandbutton from the control Toolbox Toolbar and put your code in the
click event of the commandbutton (found in the sheet module).

--

regards,
Tom Ogilvy


"Chris M" <ChrisM@discussions.microsoft.com> wrote in message
news:D6C55FFD-CF3B-45EE-AE91-24FE7FEC68F4@microsoft.com...
> I have a macro (does simple formatting) that I need to leave in the
worksheet
> object (NOT in a module). Reason for this is that I'm anticipating users
> needing to copy the worksheet into another file/new file. I need the
macro
> to continue to work within ANY copied worksheet, whether in the same
original
> file or in a separate file, INDEPENDENT of the original worksheet/file or
any
> other worksheet. The user will CLICK a BUTTON on the worksheet to run the
> macro.
>
> PROBLEM: though the macro is carried with the worksheet (which is good),
> the macro won't execute correctly, or at all (ie, the BUTTON no longer
> works). When the BUTTON is clicked, the program either tries to open the
> original file where the macro was built, or simply bombs out (error code
of
> 400).
>
> How do I ensure that when I click a "button" on the worksheet that the
macro
> assigned to it will ALWAYS work no matter where/how many times the
worksheet
> is copied?
>
> THANKS FOR ANY HELP!!!
>
>
> Chris M

Thanks Tom. Worked perfectly!
--
Chris M


"Tom Ogilvy" wrote:

> It sounds like you are using a button from the Forms Toolbar. Use a
> commandbutton from the control Toolbox Toolbar and put your code in the
> click event of the commandbutton (found in the sheet module).
>
> --
>
> regards,
> Tom Ogilvy
>
>
> "Chris M" <ChrisM@discussions.microsoft.com> wrote in message
> news:D6C55FFD-CF3B-45EE-AE91-24FE7FEC68F4@microsoft.com...
> > I have a macro (does simple formatting) that I need to leave in the
> worksheet
> > object (NOT in a module). Reason for this is that I'm anticipating users
> > needing to copy the worksheet into another file/new file. I need the
> macro
> > to continue to work within ANY copied worksheet, whether in the same
> original
> > file or in a separate file, INDEPENDENT of the original worksheet/file or
> any
> > other worksheet. The user will CLICK a BUTTON on the worksheet to run the
> > macro.
> >
> > PROBLEM: though the macro is carried with the worksheet (which is good),
> > the macro won't execute correctly, or at all (ie, the BUTTON no longer
> > works). When the BUTTON is clicked, the program either tries to open the
> > original file where the macro was built, or simply bombs out (error code
> of
> > 400).
> >
> > How do I ensure that when I click a "button" on the worksheet that the
> macro
> > assigned to it will ALWAYS work no matter where/how many times the
> worksheet
> > is copied?
> >
> > THANKS FOR ANY HELP!!!
> >
> >
> > Chris M
>
>
>