Hi Paul,

First, I appologize for not checking out my theory before
posting as I was wrong. It was late and I was tired. The
same is true tonight unfortunately. I keyed into your
post because this issue had been the bain of my existence
for some time until I arrived at my solution (which does
work).

From my observations, when you save a workbook under a
different name then the macros get reasigned to the new
workbook. If you now close the new workbook and go back
and open the original, when you push a button on the
toolbar, it will automactically open the new workbook
(contrary to what I said) in order to access the macro
code. This assumes that it can find the new workbook.

To prove that the new workbook gets opened automatically
when you push a button, check the list of open workbooks
using the Window menu item in the Worksheet Menu Bar
before and after pushing a button.

What was happening that drove me nuts was that frequently,
someone would use my workbook as a template to crunch the
numbers and then save the results (the workbook) either
to some place on our intranet, or alternatively, to a
floppy, and then walk off with the floppy. The macros
would reassign to the new workbook; i.e., to someplace on
the intranet or to a floppy that rode off into the sunset.

Therefore, when you subsequently attempted to run the
macros from the origninal workbook, Excel would look for
the code in the new workbook. But, in my case, it
couldn't find the new workbook. I'm not sure why, but
Excel couldn't access the new workbook through the
intranet and obviously couldn't do so for an absent
floppy. Therefore, I would have to manually reassign the
macros to the toolbar buttons.

What I've learned from you is that after attaching a
toolbar to a workbook, it is crucial to have code that
DELETES it on close as opposed to making it invisible.
(This is what you were doing and the exact opposite to
what I advised so I again appologize). The importance of
this is that:
i) If someone makes a copy, then it too has a record
of "Toolbar X" attached and also has code that will
delete "Toolbar X" on close. So "Toolbar X" always gets
deleted.
ii) When a workbook with an attached toolbar is opened and
it can't find the attached toolbar because it has been
deleted then it will RECREATE it. The recreated version
is the one that existed when it was first attached to that
workbook. And the toolbar button macro assignments will
of course point to macros contained in the workbook.
iii) On the other hand, if "Toolbar X" does not get
deleted then it will not get recreated and so a toolbar
with macro assignments pointing to another workbook will
persist.

This is not a brilliant insight but a major transformation
of my thinking. I never read anywhere that you should
delete the toolbar (seems like a dumb idea after all that
work). I do recall examples of making it invisible.

I did caution you that I'm only a student. I've done a
few things for my employer unpaid and study VBA for
personal development. I hope I'm not still confused and
just think I understand. What puzzles me is that the
create-on-the-fly method that I use is commonly done by
many professionals 'in order to avoid complications'.

Ironically, THANKS FOR THE HELP!!! I think I'll stop
responding to these posts until I get more experience.

So did you ever get your toolbar to work ???

Best regards,
Greg


-----Original Message-----
Greg - again, thank you for all the information. I ran
the
TestMacroAssignment() sub and you're right, it did
display the workbook name
in the macro assignment designation. But then that code
displays the macro
assignment for a menu bar item. Do you know what the
code would be to
display the same thing for the toolbar buttons?

The reason I ask is that I opened another workbook that I
created some time
ago that has a custom toolbar attached to the workbook.
This workbook
doesn't recreate the toolbar when it opens, the toolbar
is simply attached
to the workbook.

I renamed that workbook to see if the toolbar would still
open when the
workbook opens (it does), and the toolbar buttons also
function properly,
even though they're in a workbook with a different name.
So it would seem
that the macro assignments on those toolbar buttons are
not dependent on the
name of the workbook.

When I close the workbook, I delete the toolbars using
"Application.CommandBars("toolbar name").Delete" in the
Auto_Close sub. And
after that workbook is closed, the toolbar no longer
appears in the list of
toolbars in the application.

These observations seem to suggest that the toolbar goes
with the workbook.
I think one of the key steps might be that you need to
place all of the
buttons and macro assignments in the toolbar before you
Attach it to the
workbook. But it seems to work well, even after you
rename the workbook,
without having to rebuild the toolbar every time you open
the workbook.

Try it yourself as an experiment, following these steps:

- create a Sub procedure
- create a new toolbar, place a new button on the toolbar
and assign that
sub procedure to the button
- before you close the Customize dialog, go back to the
Toolbars tab and
Attach the new toolbar to the workbook.
- add this sub to a module in the workbook:
Sub Auto_Close()
On Error Resume Next
Application.CommandBars("toolbar name").Delete
End Sub
- try out the toolbar button to confirm that it's calling
the sub
- save the workbook, close then reopen, then save it with
another name
- open the renamed workbook and you'll see that the
custom toolbar is still
there

You'll also notice that after you close the workbook, the
custom toolbar no
longer appears in the list of toolbars in the
application. It's only there
when the workbook that contains the "attached" toolbar is
open.

In view of the foregoing, it seems to me that we don't
need to recreate
custom toolbars in VBA code in order to use them with a
workbook. Unless
there's something I'm missing.

Paul


.