none
In Excel, Check if Activecell is in Edit Mode RRS feed

  • Question

  • I need to verify that the active cell is not being editted (shows a blinking bar cursor). Some people have suggested using Application.EnableEvents or Application.Interactive but these are not reliable.

    
    

      IF a person is edtting data in cell, then tries to run anything that involves code that involves the Excel application object before hitting enter or exitting that cell which would take the cell out of it's edit mode, the code throws the error: 

     System.Runtime.Interopserives.COMexception (0x800A03EC):

     I need to verify whether the active cell is being edit and if it is, end it's edit proceess.  Note that I work in a COM add-in using VSTO.

     Thanks

     John G
    
    
    Tuesday, November 24, 2015 3:36 PM

Answers

  • >>>I need to verify whether the active cell is being edit and if it is, end it's edit proceess.  Note that I work in a COM add-in using VSTO.

    According to your description, you could refer to below code to check if Activecell is in Edit Mode:

    public bool IsInEditMode()
    {
          const int menuItemType = 1;
          const int newMenuId = 18;
    
          CommandBarControl newMenu = Globals.ThisAddIn.Application.CommandBars["Worksheet Menu Bar"].FindControl(menuItemType, newMenuId, Type.Missing, Type.Missing, true);
    
          return newMenu != null && !newMenu.Enabled;
    }
    

    • Marked as answer by David_JunFeng Monday, December 7, 2015 3:27 PM
    Wednesday, November 25, 2015 8:25 AM

All replies

  • Re:  detecting error mode

    I only speak VBA, so...
     Excel won't run any code while in edit mode, so just trap the error, sound a Beep and exit the code.
     Most? users understand that or are able to figure it out pretty quickly.
    -or-
    In your Ribbon code that calls the routine, check if the Selection is an Excel range; if it errors, tell the user something.
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)
    • Edited by James Cone Monday, October 31, 2016 3:42 AM
    Tuesday, November 24, 2015 4:58 PM
  • Assuming your code can run check if an EXCEL6 window exists (XLMAIN/XLDESK/EXCEL6). If it does it probably means a cell's in edit mode. If so try SendKeys Esc though you may first need to ensure Excel is on top. Might then need to give it a DoEvents. Another thing worth checking is xlApp.Ready, it won't indicate edit mode but if false wait until Ready.

    Keep in mind unless the user has gone for a coffee s/he might not like being taken out of edit mode!

    Tuesday, November 24, 2015 5:43 PM
    Moderator
  • James Cone:

    I put put the code into Try/Catch System.Runtime.InteropServices.Comexception which then throws an exception telling the user to check if the last cell they typed in is still being editted - look for a blinking bar cursor.  This works but only on certain lines of code that are trying to effect the Excel Application such as Excel.Application.SheetsInNewWorkbook=1, thus a process can get way downstream before having a reason to throw the error.  I was hoping to head it off before getting downstream.  It also sloppy because a cell being editting isn't the only problem that code throw the COMexception.

    Peter:

    The code only runs when they tell it to so they are done editting. They simpy forget to hit Enter.

    Tuesday, November 24, 2015 6:28 PM
  • >>>I need to verify whether the active cell is being edit and if it is, end it's edit proceess.  Note that I work in a COM add-in using VSTO.

    According to your description, you could refer to below code to check if Activecell is in Edit Mode:

    public bool IsInEditMode()
    {
          const int menuItemType = 1;
          const int newMenuId = 18;
    
          CommandBarControl newMenu = Globals.ThisAddIn.Application.CommandBars["Worksheet Menu Bar"].FindControl(menuItemType, newMenuId, Type.Missing, Type.Missing, true);
    
          return newMenu != null && !newMenu.Enabled;
    }
    

    • Marked as answer by David_JunFeng Monday, December 7, 2015 3:27 PM
    Wednesday, November 25, 2015 8:25 AM