Help with distributing custom toolbar

[Doug]

Hi all...I'm working on a spreadsheet that reads project management data
from a spreadsheet that's exported from an accounting application and
generates about 20 separate workbooks, one for each project manager. The
workbooks are then distributed to the individual project managers.

The Workbooks.Add method that creates the new workbook specifies a template
that contains macros & a custom toolbar that I want the users to see. But I
want the custom toolbar available only when they are using my workbook.

The problem is that Excel seems to copy the toolbar into Personal.xls when
the user opens the workbook, and the toolbar then hangs around like an
unwanted guest long after the user is finished with my workbook.

And, if next month's workbook has a new and improved toolbar, the presence
of the previous toolbar in Personal.xls prevents the new version from being
shown.

Should I write VBA code in the Auto_Open that copies the toolbar to
Personal.xls? And perhaps an Auto_Close that deletes the toolbar?

I'm a little foggy about what's really going on with Personal.xls...does
anyone know of a good website that explains how Personal.xls works and gives
pointers for dealing with distributing spreadsheets that have custom
toolbars & macros?

Thanks!

--Doug

 

[Guest]

It sounds like you have attached the toolbar to the workbook (template). It
isn't copied to the personal.xls and personal.xls has no role to play.
Commandbars are application level assests and modifications such as added
toolbars are stored in a separate binary file. In the workbook, in the
auto_close macro, put in code to delete the toolbar. This means that each
time the workbook is opened, the "new" attached toolbar will be created.
(and destroyed by your macro when the workbook is closed ).