I wrote a macro and assigned it to a button in myToolbar. My goal is to let
people to create their file based on this one and click the button to run the
macro.

First I saved it as a xls file, then I copy/paste and rename to create a new
one. After I open the new one and click the button in the toolbar, it
triggered the macro in the original xls file. Not so surprising because the
button is associated with the subroutine in the original file.

Then I saved it as a xlt file and use File New to create a xls file using
the template I jsut created. The new workbook was automatically named as
myTest1 assuming the template file name was myTest.xlt. This time when I
click the button it DID trigger the macro in the file myTest1 as expected.
However I want to give this file a meaningful name. If I save it with another
name, the button will not work anymore, similar to the first scenario it
pointed to the subroutine in myTest1, which may not even exist if I didn't
save it.

So I guess it boils down to this: How to make customized button associate
with the macro in the newly create file, not the template/seed file?

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)

JZ wrote:

I wrote a macro and assigned it to a button in myToolbar. My goal is to let
people to create their file based on this one and click the button to run the
macro.

First I saved it as a xls file, then I copy/paste and rename to create a new
one. After I open the new one and click the button in the toolbar, it
triggered the macro in the original xls file. Not so surprising because the
button is associated with the subroutine in the original file.

Then I saved it as a xlt file and use File New to create a xls file using
the template I jsut created. The new workbook was automatically named as
myTest1 assuming the template file name was myTest.xlt. This time when I
click the button it DID trigger the macro in the file myTest1 as expected.
However I want to give this file a meaningful name. If I save it with another
name, the button will not work anymore, similar to the first scenario it
pointed to the subroutine in myTest1, which may not even exist if I didn't
save it.

So I guess it boils down to this: How to make customized button associate
with the macro in the newly create file, not the template/seed file?

--

Dave Peterson

Excellent answer! Thanks you very much Dave.

"Dave Peterson" wrote:

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)

JZ wrote:

I wrote a macro and assigned it to a button in myToolbar. My goal is to let
people to create their file based on this one and click the button to run the
macro.

First I saved it as a xls file, then I copy/paste and rename to create a new
one. After I open the new one and click the button in the toolbar, it
triggered the macro in the original xls file. Not so surprising because the
button is associated with the subroutine in the original file.

Then I saved it as a xlt file and use File New to create a xls file using
the template I jsut created. The new workbook was automatically named as
myTest1 assuming the template file name was myTest.xlt. This time when I
click the button it DID trigger the macro in the file myTest1 as expected.
However I want to give this file a meaningful name. If I save it with another
name, the button will not work anymore, similar to the first scenario it
pointed to the subroutine in myTest1, which may not even exist if I didn't
save it.

So I guess it boils down to this: How to make customized button associate
with the macro in the newly create file, not the template/seed file?

--

Dave Peterson

That was no help!

"JZ" wrote:

I wrote a macro and assigned it to a button in myToolbar. My goal is to let
people to create their file based on this one and click the button to run the
macro.

First I saved it as a xls file, then I copy/paste and rename to create a new
one. After I open the new one and click the button in the toolbar, it
triggered the macro in the original xls file. Not so surprising because the
button is associated with the subroutine in the original file.

Then I saved it as a xlt file and use File New to create a xls file using
the template I jsut created. The new workbook was automatically named as
myTest1 assuming the template file name was myTest.xlt. This time when I
click the button it DID trigger the macro in the file myTest1 as expected.
However I want to give this file a meaningful name. If I save it with another
name, the button will not work anymore, similar to the first scenario it
pointed to the subroutine in myTest1, which may not even exist if I didn't
save it.

So I guess it boils down to this: How to make customized button associate
with the macro in the newly create file, not the template/seed file?