Assigned macros change location

[Guest]

I have a workbook (named Form.xls) with a number of macros, each of which is
assigned to a button on a custom toolbar. I import data from another program
into this workbook, then use the macros to format the data into Customized
Reports. I want to then save the workbook with a new name (I use today's date
as the name, eg 03.29.06) so that I have an archive of the reports produced.

My problem is that when I use File, Save As to rename Form.xls, the Toolbar
buttons in Form.xls all change their assignments to the new file's name. So
the next time I open Form.xls, the new file (ie 03.29.06) also opens so that
the macros are available.

Is there a way to avoid having this re-assignment occur?

Thanks

 

[Dave Peterson]

Build your toolbar when the workbook is opened and destroy it 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)

 

[Guest]

Thanks Dave. (Sorry not to respond sooner - the email notification went into
my Spam folder.) This sounds as if it functions more or less as the personal
workbook macro file, but doesn't have to actually open every time you start
Excel. Busy time of the year for me right now, so I may not have a chance to
try all of this for a couple of weeks, but it seems to do all I was hoping
for.

Thanks again!

 

[Dave Peterson]

Glad you got it working.

Thanks Dave. (Sorry not to respond sooner - the email notification went into
my Spam folder.) This sounds as if it functions more or less as the personal
workbook macro file, but doesn't have to actually open every time you start
Excel. Busy time of the year for me right now, so I may not have a chance to
try all of this for a couple of weeks, but it seems to do all I was hoping
for.

Thanks again!