Copy macro enabled button to another file

[Jen]

Greetings,

I set up a macro-enabled button on one file(say A file).

I would like to copy the button to another file(say B file).
I find that I can copy the VBA module from VBA editor.
However, when the button was copied to the B file,
the macro on the button has the name of "A.xls!macro_name".
Then I have to delete "A.xls!" for the macro.

Can I just copy the macro name only, but not the previos file name that the
macro came from?

I would like to use that button to more than 200 files... It would be a lot
of work to change the macro name...

Thanks for your help in advance,
Regards,

Jen

 

[Dave Peterson]

If this is a button from the Forms toolbar that was placed onto a worksheet,
then you'll find that using the commandbutton from the Control Toolbox toolbar
fits much nicer.

But the code will have to be moved and probably modified to use this
commandbutton.

If you copy that worksheet to a new workbook, then the button will travel with
the worksheet. But more importantly, the worksheet module that contains the
macro will move, too.

That said, I don't think it's a good idea to do it this way. If the code has to
change, then you'll have to change it in those 200 workbooks (and all the
workbooks that could have been created based on one of those 200!).

Instead, I think I'd create an addin and create a toolbar with the buttons I
need. In xl2007, you'll see this toolbar in the Addins tab.

Saved from a previous post:

Your life will become much simpler if you include code to create the toolbar
when the workbook is opened and include code to destroy the toolbar when the
workbook is closed.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

In xl2007, those toolbars and menu modifications will show up under the addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

 

[Jen]

Hi Dave,
Thanks for giving a new idea!! I never think about that way..
I will try!

Thanks again,

Jen