View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Bill Renaud[_2_] Bill Renaud[_2_] is offline
external usenet poster
 
Posts: 117
Default Excel VBA - Excel crashes when macro button re-assigned problem

I would basically put the code and toolbar in a new workbook, separate from
all of your data workbooks. This has the advantage of reducing the size of
all of your data workbooks. You can easily convert this toolbar workbook to
an add-in, if you want it to load when you start Excel, rather than when you
click a toolbar button the first time.

You will have some work to do to replace most of the references to
ActiveWorkbook, ActiveSheet, and ActiveCell, over to equivalent code, but
the end result is better programming practice anyway.

Remember to delete the toolbar from the Excel toolbar area before opening
the new toolbar workbook, or confusion will still remain. Don't just "hide"
the toolbar by clicking the "X" at the upper right corner. You must bring up
the Customize dialog box and use the Delete button on the Toolbars tab.

Constantly changing the workbook that a toolbar is attached to has always
been problematic in Excel for me also, for toolbars attached to data
workbooks that are renamed, moved to other folders, etc.
--
Regards,
Bill