commandbar management

[me]

Hi, I am having problems with custom commandbars. In the Open event I create
a custom cb. I delete it in the BeforeClose event. If I open a second file
that has the same code before closing the first, they collide and the 2nd
one isn't created. If I then close the first, the cb is deleted so I have to
reopen the 2nd or run the code manually to create it. If I remove the code
to delete the cb beforeclose, only the first one created stays open and any
commands run run on the FIRST instance opened, even if it has been closed.
Do I have to create the cb with a unique name, such as the name of the file,
to avoid this? I'm creating many files from a single file with the code. Do
I need to implement the code in a template, or something else?

Many thanks,
Mike

 

[Dave Peterson]

If the commandbars are separate and distinct, then use unique names.

If the commandbars do the same thing, I think I would provide an addin that
built the toolbar and remove it from each of the workbooks. By separating the
code from the workbooks, it may become even easier to update when (not if)
things change.

 

[me]

Thanks Dave. That seems to make good sense, but it's uncharted territory for
me. Can you point me to a simple Add-in example, preferably with the
CommandBar implementation you're suggesting?
Cheers,
Mike

 

[Dave Peterson]

If you want to add an option 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://groups.google.co.uk/[email protected]