Click here to Skip to main content
15,906,624 members
Articles / Productivity Apps and Services / Microsoft Office

Determining if Excel is in Edit mode

Rate me:
Please Sign up or sign in to vote.
2.41/5 (7 votes)
29 Aug 2007CPOL2 min read 65.7K   12   3
A small code sampe for 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" );


  • Original article - 8/29/2007.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Web Developer
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

GeneralMy vote of 5 Pin
David Burgess 202329-Sep-23 15:21
David Burgess 202329-Sep-23 15:21 
GeneralMy vote of 5 Pin
bccgroup25-Feb-11 0:18
bccgroup25-Feb-11 0:18 
GeneralI can't believe how horrible the VSTO API is Pin
Philipp Sumi3-Jan-10 5:53
Philipp Sumi3-Jan-10 5:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.