Determining if Excel is in Edit mode

Introduction & Background

One of the trickiest parts of programming to the Excel object model is determining if you are in "Edit mode". Edit mode is when a cell is accepting input. The user can get into edit mode by clicking in a cell, clicking on the formula bar, or hitting the F2 key. Why is this problematic? When you try to run a piece of code (i.e., set a cell's Value2 property) while you are in edit mode, an Exception is thrown.

Solution Code

Essentially, the problem is determining if we are in Edit mode. It would be nice to have an event that would say that the application is in Edit mode. This way, you could disable your UI appropriately on the event. Since that doesn't exist, you'll have to check if Excel is in Edit mode before you make calls to the automation object. The Application.Ready property (MSDN) is supposed to help you determine if the Excel automation object is ready for an action, but using .NET 2.0 and the Office PIAs, this property seems to always return true whether or not you are in Edit mode (at least, I've never seen different).

By looking at the disabled state of the Command bars in Excel 2003, Excel knows if you are editing a cell, but doesn't share this information with the object model. A simple way to check if you are in Edit mode would be to check the enabled status of the menu items on the CommandBar. In order to do this, we have to find the control and check its enabled state. (Yes, this solution even works in 2007 - because the CommandBars are still intact, just hidden from the UI.) However, there exists a caveat: that is, if the user has removed the New menu from the Command bar, then you will not find it. At that point, you have to assume Excel is not in Edit mode - having said that, this solution is probably OK in 99% of the cases.

The New command resides on the "Worksheet Menu Bar" - and we can call FindControl to locate the exact menu item we're looking for, in this case, the New menu item. We want to look for it recursively so the the last argument should be true. The following code snippet will allow you to determine if Excel is in edit mode. It will throw an Exception, but alternatively you could return a boolean.

object m = Type.Missing;
const int MENU_ITEM_TYPE = 1;
const int NEW_MENU = 18;

CommandBarControl oNewMenu = 
  Application.CommandBars["Worksheet Menu Bar"].FindControl(
       MENU_ITEM_TYPE, //the type of item to look for
       NEW_MENU, //the item to look for
       m, //the tag property (in this case missing)
       m, //the visible property (in this case missing)
       true ); //we want to look for it recursively
               //so the last argument should be true.

if ( oNewMenu != null )
{
    if ( !oNewMenu.Enabled )
    {
        throw new Exception( "Excel is in Edit Mode" );
    }
}

History

• Original article - 8/29/2007.