Remove custom menu

P

Partho

I have a custom menu which appears as a menu item in the worksheet menu bar
whenever this spreadsheet is opened. This menu itm is supposed to be visible
when the spreadsheet is activated and disappear when another spreadsheet is
activated. I am using Excel 2003.

The code for adding the menu is in the ThisWorkbook code module within
Sub Workbook_Activate().

The code for removing the menu is in the ThisWorkbook code module within
Sub Workbook_Deactivate().

In each case the code in ThisWorkbook calls routines within the main macro
modules.

The code in the main macro module for removing the menu is:
CommandBars("Worksheet Menu Bar").Controls("MyMenu").Delete

The problem is that when another spreadsheet is opened whilst this one is
open, the custom menu item is not disappearing, although the newly opened
spreadsheet is obviously the one that becomes the active one.

Is there any way that the removal of the custom menu can be more robust?

Thanks.
 
J

Jacob Skaria

Dear Partho

Please try removing the custom menu in WindowDeactivate event..

If this post helps click Yes
--------------
Jacob Skaria

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
'Close here
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top