3

I have an Excel AddIn. I add 2 context menu items in cell's context menu.

When you right click a cell, based on the formula of the cell, one context menu item will be disabled.

I put code for this in sheetSelectionChangeEvent

This works fine in Excel 2003, 2007 and 2010 but it does not work in Excel 2013.

Below is the code:

 private void ApplicationSheetSelectionChange(COMObject sh, Range target)
 {
     DisableMenubarsButtonsWRibbon(XLApp.Selection as Range);
 }

 public void DisableMenubarsButtonsWRibbon(Range rng)
 {
     var formula = rng.Formula as string;
     if(formula is function1)
     {
         _contextMenuItem1.Enabled = true;
         _contextMenuItem2.Enabled = false;
     }
     else if(formula is function2)
     {
         _contextMenuItem1.Enabled = false;
         _contextMenuItem2.Enabled = true;
     }
     else
     {
         _contextMenuItem1.Enabled = true;
         _contextMenuItem2.Enabled = true;
     }
 }
Andrii Kalytiiuk
  • 1,501
  • 14
  • 26
toosensitive
  • 2,335
  • 7
  • 46
  • 88
  • Found [your post on MSDN](http://social.msdn.microsoft.com/Forums/ie/en-US/4d45a170-9830-45c0-a05e-92153b240e37/dynamic-disableenable-custom-context-menu-in-excel-2013-is-not-working?forum=exceldev). You have not stated here that you used *ExcelDNA* + *NetOffice* open source libararies. I had the same issue with VSTO - as a solution it helped to find context menu item by label each time you need to change its enabled state. Problem appeared to be in some manipulations with worksheets that were performed after adding menu items. Problem does not reproduce for me with sample project. – Andrii Kalytiiuk Jan 09 '14 at 13:32

1 Answers1

2

Reasons of behavior you reported:

Behavior you have reported is due to the bug of MS Excel 2013 core.

Behavior is not due to the libriaries/toolkits for creating Excel add-ins as:

  • I experience the same problem with VSTO 2010 while you report your issue for ExcelDNA and NetOffice
  • In both cases enabling of context menu items worked well with Office 2010 and stopped working with Office 2013.

I have submitted the defect for Visual Studio and .NET Framework area on MS Connect as I have not found where they accept bugs for Office.

As on MS Connect some times links to defects appear broken - I will add details of the defect with screenshots in the end of this answer.

Work around - to enable disable menu item

As it was stated in answer to your question on MSDN Social forum - you can control Enabled state of context menu items when you retrieve items by Caption or by Tag property:

CommandBar contextMenu = Globals.ThisAddIn.Application.CommandBars["Cell"];
foreach (CommandBarControl control in contextMenu.Controls)
{
    if (control.Caption == "item caption") // Specify here caption of your context menu item
    {
        contextMenuItem = (CommandBarButton)control;
        contextMenuItem.Enabled = true; // Specify here desired value for Enabled state to be set
    }
}

In above code snipped context menu item is found by Caption - to find item by Tag replace code:

    control.Caption == "item caption"

in if condition with:

    control.Tag == "item tag"

specifying proper value instead of item tag text

Defect details

MS Connect ticket has ID 813453 and title:

Enabled state does not change for context menu items of Excel 2013 add-in created with C# .NET and Visual Studio 2012

Below are screenshots of the ticket: 813453 Page 1 813453 Page 2 813453 Page 3 813453 Page 4

Community
  • 1
  • 1
Andrii Kalytiiuk
  • 1,501
  • 14
  • 26
  • Thanks, Andrii. I guess I speak too soon. I tried the workaround and it does not work for me. – toosensitive Feb 11 '14 at 22:26
  • @toosensitive That's predictable - you use different framework for Excel extensions. You should probably *contact vendors/debug source code* of your framework to find out how to properly access context menu items. Nevertheless reason seems to be the same - a defect in Excel. – Andrii Kalytiiuk Feb 12 '14 at 14:06