Link to home
Start Free TrialLog in
Avatar of akohan
akohan

asked on

How to remove a menu from Excel ?!!

Hello group,

I have written a code using COM Addin to add a new menu item on Excel menu bar. It has added a menu after "Help" menu. In the code,  I made a mistake by passing a wrong
parameter so instead of having Excel to consider the menu as a temporary item it is considered as permanent.

Now, everytime I run Excel it is there. How can I remove this item?


Regards,
ak
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Hello akohan,

Select Tools|Customize from the menu, rightclick the menu item, and select delete.

Regards,

Patrick
Hi akohan,

Or, if this is a new item on the standard menu bar, try this:

Sub ResetWorksheetMenu()
     Application.CommandBars("Worksheet Menu Bar").Reset
End Sub

Jim
Avatar of akohan
akohan

ASKER



Hi Jim,

Thanks for your response. This must have a different approach since it doesn't go away this way. This is a custom menu item I added by a code. I tried your advice but didn't work.


Any comments?

Regards,
ak
Avatar of akohan

ASKER



This is what I have done:

               ExcelApp.CommandBars(1).Controls.Add(Type:=msoControlPopup, Temporary:=False)

As you see, I didn't pay attention first and passed False instead of True in the 2nd argument.



Any suggestions?

Thanks.
ak
ak,

It should work.  CommandBars(1) is the same as CommandBars("Worksheet Menu Bar").  Here's a sample rotine that adds a new control to the Worksheet Menu Bar (like you did) and then resets it back.

Sub ResetWorksheetMenu()
Dim ExcelApp As Excel.Application, cb As CommandBarControl

Set ExcelApp = Application

Set cb = ExcelApp.CommandBars(1).Controls.Add(Type:=msoControlPopup, Temporary:=False)

cb.Caption = "New Menu Item"
cb.Visible = True
   
ExcelApp.CommandBars(1).Reset

End Sub

Try this and let me know how it works for you.

Jim
Avatar of akohan

ASKER


Hi again Jim,

I ran the code as you said "ExcelApp.CommandBars(1).Reset" but still it is there!!!

The thing is that I have " Adobe PDF" menu too and it always appears when Excel is done with its own menus, I can see a tiny delay and my menu had same behviour but since I added them as permanent they just come up exactly with the speed of regular Excel menu items. it is like the menu now is part of Excel menu :) so confusing.

Thanks.
ASKER CERTIFIED SOLUTION
Avatar of akohan
akohan

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator