Custom excel right click context menu gets overwritten by Quick Analysis defaults

c#, excel, vsto

Solution

Excel uses a separate right-click menu for Tables.

I only speak VBA, so you will have to translate...

When right-clicking in a "normal" cell the `CommandBars("Cell")` menu is used.

When right-clicking in a Table the `CommandBars("List Range Popup")` menu is used.

Problem

I'm following this example to create a custom rightclick custom menu for an excel add-in with VSTO and display it under certain conditions (rightclick inside a range of an Excel named table). My modified version of the code from the example works like a charm when I right click outside a named table range: but it doesn't get displayed when you right click inside a named table range: I suppose it has something to do with the Quick Analysis functionality interfering with my custom context menu overrides. Here is the code I'm using inside ThisAddin.cs: ``` void Application_SheetBeforeRightClick(object worksheet, Excel.Range range, ref bool cancel) { GetCellContextMenu().Reset(); // reset the cell context menu back to the default // If the selected range belongs within a named excel table we display the refresh menu item at the right click context menu. if (true) //range.IntersectsWithAnyExcelTable()) <-- this code works fine but I commented it out for the purpose of showing the problem (in this case the custom popup meny should appear ALWAYS): { const OfficeCore.MsoControlType menuItem = OfficeCore.MsoControlType.msoControlButton; var refreshMenuItem = (OfficeCore.CommandBarButton)GetCellContextMenu().Controls.Add(menuItem, missing, missing, 1, true);// where missing = global::System.Type.Missing; refreshMenuItem.Style = OfficeCore.MsoButtonStyle.msoButtonCaption; refreshMenuItem.Caption = "Refresh My Data"; refreshMenuItem.Click -= RefreshMenuItemClick; refreshMenuItem.Click += RefreshMenuItemClick; } } ``` and don't forget to subscribe the event when the add-in is started: ``` Application.SheetBeforeRightClick += Application_SheetBeforeRightClick; ``` How can I either: Display my custom menu despite Quick Analysis kicking in. Override quick analysis Refresh Button functionality (afaiu this is impossible.)

Original source

Related problems