View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Rich[_13_] Rich[_13_] is offline
external usenet poster
 
Posts: 3
Default Problem with macros assigned to toolbar buttons

Greg,
Thanks for the advice.
Since I already had an Auto_Close event to turn off the
toolbar visibility, I just changed the *.visibility=false
to *.delete.
Worked like a champ. Again, Thanks.

Rich

-----Original Message-----
I believe this is usually resolved by having code in the
Workbook_Open event that programmatically creates a
temporary custom toolbar (Temporary parameter set to
True). The macros are also assigned to the buttons at

the
same time. Because its status is set to Temporary, when
the workbook is closed it automatically deletes the

custom
toolbar. The Workbook_Open event code automatically
creates it again when it is opened. The same holds for
any copy made of the workbook. So the referenced macros
for the toolbar buttons always point to that workbook.

The second method is to create the toolbar and then

attach
it to the workbook (Tools|Customize|Toolbars tab|Select
toolbar|Attach option). Then use the

Workbook_BeforeClose
event or AutoClose macro to delete the toolbar on close.
When the workbook is subsequently opened and Excel cannot
find the toolbar because it has been deleted then it will
recreate the toolbar according to its status when it was
attached including macro references. This is true for
copies of the workbook also.

An important point is that attached toolbars are not
updated after being attached. So changes made to an
attached toolbar will not take effect. To make changes

to
an attached toolbar, unattach it, make the changes and
reattach it.

Regards,
Greg