自定义excel右键单击上下文菜单被Quick Analysis默认值覆盖

问题描述

我正在关注。）

解决方案

Excel使用单独的右键单击菜单。

我只会说VBA，所以你必须翻译...

当在普通单元格右键单击 CommandBars（Cell）菜单。

当在表中右键单击 CommandBars （List Range Popup）菜单。

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:

1. Display my custom menu despite Quick Analysis kicking in.

2. Override quick analysis Refresh Button functionality (afaiu this is impossible.)

解决方案

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.

这篇关于自定义excel右键单击上下文菜单被Quick Analysis默认值覆盖的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！