Removing a custom menu 1

[Zubinsum]

I am running Excel2000. I had a workbook with a macro for a custom menu, but now this custom menu appears on all my workbooks, including new ones. The workbook containing the macro has been deleted and I can't see any code that would bring up this menu.

Anyone have any ideas on how I can fix this problem?

 

[rmikesmith]

Zubinsum,

Perhaps a silly question, but have you tried simply deleting this menu? Unless your VBA code (macros) explicitly deletes custom commandbars, they remain from session to session. That is also the problem with Attached commandbars. They load with your workbook but remain after it closes. It's best to create/destroy custom commanbars in code.

Regards,
Mike

 

[Zubinsum]

Rmike:

Yes, I have tried that. The new menu appears even tho there is no code for it. The menu is unaffected by a delete menu macro (or command).

Thanks for the input.

-Zubin

 

[Zubinsum]

Ok, I got it.

I found a typo in my remove menu macro.

Thanks again Rmike.

 

[rmikesmith]

Zubin,

Just a follow-up: You can also manually delete a custom commandbar (menubar or toolbar); right-click on the menubar or toolbar region and choose Customize... Select the Toolbars tab, scroll down and find your menubar, click to highlight it then click the Delete button.

Regards,
Mike

 

[Zubinsum]

Mike:

Thanks agin for the tip!

-Zubin

 

[tuxalot]

I have a similar macro. This solution is nice since if you toggle between two workbooks but only wish to have the custom menu present in one of the two, your menu will only be available from the correct workbook. This is accomplished with the use of the following "AddMenus" and "DeleteMenu" macros, that fire with the Workbook_Activate and Workbook_Deactivate objects. First, add the following code to your ThisWorkbook Object:

Private Sub Workbook_Activate()
Run "AddMenus"
End Sub

And the next one...

Private Sub Workbook_Deactivate()
Run "DeleteMenu"
End Sub

Then, create a new module and add this code for the add and delete macros.

Sub AddMenus()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl

'(1)Delete any existing one. We must use On Error Resume next _
in case it does not exist.
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar&quot.Controls("&your name here&quot.Delete
On Error GoTo 0

'(2)Set a CommandBar variable to Worksheet menu bar
Set cbMainMenuBar = _
Application.CommandBars("Worksheet Menu Bar&quot

'(3)Return the Index number of the Help menu. We can then use _
this to place a custom menu before.
iHelpMenu = _
cbMainMenuBar.Controls("Help&quot.Index

'(4)Add a Control to the "Worksheet Menu Bar" before Help.
'Set a CommandBarControl variable to it
Set cbcCutomMenu = _
cbMainMenuBar.Controls.Add(Type:=msoControlPopup, _
Before:=iHelpMenu)

'(5)Give the control a caption
cbcCutomMenu.Caption = "&your name here"

'(6)Working with our new Control, add a sub control and _
give it a Caption and tell it which macro to run (OnAction).
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Show Navigation Bar"
.OnAction = "ShowufMain"
End With
'(6a)Add another sub control give it a Caption _
and tell it which macro to run (OnAction)
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Blank Menu"
.OnAction = ""
End With
'Repeat step "6a" for each menu item you want to add.


'Add another menu that will lead off to another menu
'Set a CommandBarControl variable to it
Set cbcCutomMenu = cbcCutomMenu.Controls.Add(Type:=msoControlPopup)
' Give the control a caption
cbcCutomMenu.Caption = "Ne&w Menu"

'Add a contol to the sub menu, just created above
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "&Charts"
.FaceId = 420
.OnAction = ""
End With

Set cbMainMenuBar = Nothing
Set cbcCutomMenu = Nothing

End Sub

Sub DeleteMenu()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar&quot.Controls("&your name here&quot.Delete
On Error GoTo 0

End Sub



Give this a try.
Sky