Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with macros assigned to toolbar buttons
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 -----Original Message----- I have a custom toolbar with macros assigned to the buttons. The name of the macro assigned to the button has the tempalte workbook name referenced to it. When I create a new workbook from the template, the workbook reference for the macro changes. If I close the new workbook (by closing excel), and open either the template or a previously created woorbook, the workbook reference of the macro is the last workbook that was created. When I click on the button, excel opens the last workbook created and runs the macro there...not in the "current" workbook. Is there a way to omit the workbook reference that preceeds the macro name where you assign a macor name to a button? Is there such a thing as a "local" macro? Hope this makes sense...my cold medicine is making me feel a little loopy. Thanks in advance. Rich . |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 Custom toolbar problem saving new buttons | Excel Discussion (Misc queries) | |||
Assigning Macros to Toolbar buttons | Excel Discussion (Misc queries) | |||
Macro assigned to Toolbar problem | Excel Discussion (Misc queries) | |||
How can you replicate macros for buttons on the Standard toolbar? | Excel Discussion (Misc queries) | |||
Why does macros assigned to a toolbar lose its link when saved as | Excel Discussion (Misc queries) |