save and restore "Workbook Menu Bar" & "Cell" menus

I have a workbook where I have overridden several of the CommandBarButtons on
the standard "Workbook Menu Bar" and "Cell" CommandBars.

I have taken the drastic measure of resetting "Workbook Menu Bar" and "Cell"
CommandBars prior to making my changes and then resetting again in the
Workbook_Deactivate sub. I've taken this measure to prevent errors if for
some reason the particular CommandBarButtons that I'm changing are Nothing or
Not Enabled etc. This of coarse wipes out any customizations my users might
have made to their Menus, probably making them not happy.

I would like to somehow save the state of the "Workbook Menu Bar" and "Cell"
CommandBars on entry to my workbook and then restore it on
Workbook_Deactivate but I cannot figure out how.

Can someone tell me how to accomplish this?

http://groups.google.co.uk/advanced_group_search?hl=en

If you destroy customizations made by another application, there will be no
way to restore them if they used customized icons - well, it would be very
involved. Why not leave things along and test what you want to change to see
if it will cause problems. And when Excel crashes before your code
restores things? I would look at disabling and adding to rather than
removing or destroying. That is how excel operates.

--
Regards,
Tom Ogilvy


"Jeff Higgins" <[email protected]> wrote in message
news:[email protected]...
> I have a workbook where I have overridden several of the CommandBarButtons
on
> the standard "Workbook Menu Bar" and "Cell" CommandBars.
>
> I have taken the drastic measure of resetting "Workbook Menu Bar" and
"Cell"
> CommandBars prior to making my changes and then resetting again in the
> Workbook_Deactivate sub. I've taken this measure to prevent errors if for
> some reason the particular CommandBarButtons that I'm changing are Nothing
or
> Not Enabled etc. This of coarse wipes out any customizations my users
might
> have made to their Menus, probably making them not happy.
>
> I would like to somehow save the state of the "Workbook Menu Bar" and
"Cell"
> CommandBars on entry to my workbook and then restore it on
> Workbook_Deactivate but I cannot figure out how.
>
> Can someone tell me how to accomplish this?
>
>

Tom,
Thanks again for excellent advice. Of course you are right in advising me to
rethink the way I'm doing this. I'm busy now writing a bunch of If Then tests
and will use the disable/add paradigm rather than slash & burn.

Thanks also for the link, a search on: save restore excel menus, produced a
plethora of links that reinforced exactly your advice.

Appreciate the help,
Jeff Higgins


"Tom Ogilvy" wrote:

> http://groups.google.co.uk/advanced_group_search?hl=en
>
> If you destroy customizations made by another application, there will be no
> way to restore them if they used customized icons - well, it would be very
> involved. Why not leave things along and test what you want to change to see
> if it will cause problems. And when Excel crashes before your code
> restores things? I would look at disabling and adding to rather than
> removing or destroying. That is how excel operates.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Jeff Higgins" <[email protected]> wrote in message
> news:[email protected]...
> > I have a workbook where I have overridden several of the CommandBarButtons
> on
> > the standard "Workbook Menu Bar" and "Cell" CommandBars.
> >
> > I have taken the drastic measure of resetting "Workbook Menu Bar" and
> "Cell"
> > CommandBars prior to making my changes and then resetting again in the
> > Workbook_Deactivate sub. I've taken this measure to prevent errors if for
> > some reason the particular CommandBarButtons that I'm changing are Nothing
> or
> > Not Enabled etc. This of coarse wipes out any customizations my users
> might
> > have made to their Menus, probably making them not happy.
> >
> > I would like to somehow save the state of the "Workbook Menu Bar" and
> "Cell"
> > CommandBars on entry to my workbook and then restore it on
> > Workbook_Deactivate but I cannot figure out how.
> >
> > Can someone tell me how to accomplish this?
> >
> >
>
>
>