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

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

i thought so...for now it is the only solution that does not use any sendkeys or loop or something dirty eatin up your processor time.