Workaround to see if Excel is in cell-edit mode in

Question

I have an application written in VB.NET that interacts with Excel via interop. I eventually ran into the known issue of Cell-edit mode (see MSDN and stackoverflow for some background).

I have been trying to convert the suggested code to VB.NET but keep getting the following error:

Reference required to assembly 'office, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' containing the type 'Microsoft.Office.Core.CommandBars'. Add one to your project. (BC30652) - E:\ ... .vb:3471

The original C# code (from previosuly mentioned articles) is as follows

private bool IsEditMode()
{
   object m = Type.Missing;
   const int MENU_ITEM_TYPE = 1;
   const int NEW_MENU = 18;

   // Get the "New" menu item.
   CommandBarControl oNewMenu = Application.CommandBars["Worksheet Menu Bar"].FindControl(MENU_ITEM_TYPE, NEW_MENU, m, m, true );

  if ( oNewMenu != null )
  {
     // Check if "New" menu item is enabled or not.
     if ( !oNewMenu.Enabled )
     {
        return true;
     }
  }
  return false;
}

My converted VB.NET code is as follows

Private Function isEditMode() As Boolean
    isEditMode = False
    Dim m As Object = Type.Missing
    Const  MENU_ITEM_TYPE As Integer = 1
    Const  NEW_MENU As Integer = 18

    Dim oNewMenu As Office.CommandBarControl
    ' oExcel is the Excel Application object 
    ' the error is related to the below line
    oNewMenu = oExcel.CommandBars("Worksheet Menu Bar").FindControl(MENU_ITEM_TYPE, NEW_MENU, m, m, True)
    If oNewMenu IsNot Nothing Then
        If Not oNewMenu.Enabled Then
            isEditMode = True
        End If
    End If
End Function

I have added a (COM) reference to the Microsoft Office Object Library

Imports Office = Microsoft.Office.Core
Imports Microsoft.Office.Interop

I am kind of stuck. I already have tried indirectly referencing the CommandBar object, and re-adding refrences but can not figure out what is the problem. any ideas?

Answer 1

As a quick-and-dirty fix i used the following code as an alternative

Private Function isEditMode() As Boolean
    isEditMode = False
    Try
        oExcel.GoTo("###")
    Catch Ex As Exception
       ' Either returns "Reference is not valid." 
       ' or "Exception from HRESULT: 0x800A03EC"
       If ex.Message.StartsWith("Exception") then isEditMode  = True
    End Try     
End Function

The .GoTo function (and corresponding menu item) is not available when Excel is in Cell-edit mode. Giving the .GoTo function a dummy destination will do nothing and won't affect anything if the user is working in the cell when the code runs.

An added extra is that no reference to the Microsoft Office Object (Microsoft.Office.Core) library is needed.

Answer 2

Function ExcelIsBusy()
ExcelIsBusy = Not Application.Ready
Dim m
m = Empty
Const MENU_ITEM_TYPE = 1
Const NEW_MENU = 18

Dim oNewMenu
Set oNewMenu = Application.CommandBars("Worksheet Menu Bar").FindControl(MENU_ITEM_TYPE, NEW_MENU, m, m, True)
If Not (oNewMenu Is Nothing) Then
    If Not oNewMenu.Enabled Then
        ExcelIsBusy = True
        'throw new Exception("Excel is in Edit Mode")
    End If
End If

End Function

Answer 3

We previously used the Application.CommandBars["Worksheet Menu Bar"] method but we encountered a flaw. When exiting Excel while in edit mode, edit mode cancels out, but the function still returns true because the menu items have been disabled as part of the shutdown.

We used the following solution instead:

public static bool ApplicationIsInEditMode(Application application)
{
    try
    {
        application.ReferenceStyle = application.ReferenceStyle;
    }
    catch (COMException e)
    {
        return true;
    }
    return false;
}

Answer 4

Following code will detect whether excel is in Edit Mode and will exit out of it :

private void exitEditMode()
{

    if (!isExcelInteractive())
    {
        // get the current range
        Excel.Range r = Globals.ThisAddIn.Application.ActiveCell;
        // bring Excel to the foreground, with focus
        // and issue keys to exit the cell
        xlBringToFront();
        Globals.ThisAddIn.Application.ActiveWindow.Activate();
        SendKeys.Flush();
        SendKeys.SendWait("{ENTER}");
        // now make sure the original cell is
        // selected…
        r.Select();
    }
}

private bool isExcelInteractive()
{
    try
    {
        // this line does nothing if Excel is not
        // in edit mode. However, trying to set
        // this property while Excel is in edit
        // cell mdoe will cause an exception
        Globals.ThisAddIn.Application.Interactive = Globals.ThisAddIn.Application.Interactive;
        return true; // no exception, ecel is 
        // interactive
    }
    catch
    {
        return false; // in edit mode
    }
}

private void xlBringToFront()
{
    SetForegroundWindow(Globals.ThisAddIn.Application.Hwnd);
}

[DllImport("User32.dll")]
public static extern Int32 SetForegroundWindow(int hWnd);