"Strange" behaviour when programatically disabling/re-enabling commandbar controls

Hi folks,

I am working on an XL2003 addin which requires me to temporarily disable some commandbar controls whilst an associated workbook is active, and re-enable them once that book is closed or a non-associated book is activated.

To do this I am assigning the Application object to a WithEvents object variable and doing the disabling/re-enabling by trapping the _WorkbookActivate and _WorkbookBeforeClose events on that object.

The controls all re-enable fine, but if I then quit Excel and restart it, any custom (i.e. non-built in) controls that have been disabled/re-enabled in this way disappear completely as if they have been deleted.

Any ideas as to why this might be, and/or how to stop it from happening?

Hi blackworx,
Had a similar problem and solved it by using both

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

to control the commandbar

Hi pike,

Thanks for your reply.

Not sure what you mean as the code isn't part of the workbook(s) in question - they are just dumb workbooks which don't have any code of their own. The code controlling the toolbars is part of the addin and it runs whenever there is any change in the current active workbook.

Please Login or Register  to view this content.

The code itself works fine, returning all toolbars and controls to the correct state, but when I subsequently restart Excel some controls associated with other addins or the user's custom controls have disappeared. This even happens when I close all workbooks (including my add-in) before restarting Excel. Everything is present and correct just before I close Excel, but when I restart it some controls are gone. Very odd!

EDIT: Just realised I hadn't pointed out explicitly that it's only custom controls that have been added to Excel's built-in commandbars that disappear. Any and all custom commandbars belonging to the user or other workbooks are not affected. It's as if the disabling/re-enabling somehow causes Excel to reset its built-in commandbars, but only when Excel itself is restarted.