Andrei Smolin

How to check programmatically if the user is editing an Excel cell

Here’s a typical scenario: your code modifying a workbook fails because the user types something into a cell. Now how to determine that Excel is in the edit mode?

A non-solution

The code below is based on the fact that certain command bar controls in Excel become disabled when you type in a cell. In the UI of Excel 2000-2003, you can find heaps of commandbar controls that become disabled in this scenario. Here is a code sample that uses one of such control:

' wrong version
    Private Function IsEditing () As Boolean
        Dim cBars As Office.CommandBars = ExcelApp.CommandBars
        Dim cBar As Office.CommandBar = cBars.Item("Standard")
        Dim cControls As Office.CommandBarControls = cBar.Controls
        Dim cControl As Office.CommandBarControl = cControls.Item("New")
        Dim result As Boolean = Not cControl.Enabled
        Marshal.ReleaseComObject(cControl)
        Marshal.ReleaseComObject(cControls)
        Marshal.ReleaseComObject(cBar)
        Marshal.ReleaseComObject(cBars)
        Return result
    End Function

Although the code above does work in Excel 2000-2010 (because the command bar controls are just hidden from the user), it is subject to many issues: it depends on the availability of the command bar and control. The code can be further modified to use the CommandBars.FindControl or CommandBar.FindControls method but this doesn’t help because the user or some other Office extension may move or delete the command bar or the control, or both. As you understand, this also relates to the Ribbonned Excel versions. So, using the disabled state of a command bar control to check if Excel is in the edit mode is a wrong way.

A solution for Excel 2007 – 2010

In Excel 2007-2010, you may check the enabled status of a built-in control in the Ribbon way:

Imports System.Windows.Forms
Imports Office = Microsoft.Office.Core
Imports Excel = Microsoft.Office.Interop.Excel
...
    ' Ribbon version
    Private Function IsEditing () As Boolean
        Dim cBars As Office.CommandBars = ExcelApp.CommandBars
        Dim result As Boolean = Not cBars.GetEnabledMso("FileNewDefault")
        Marshal.ReleaseComObject(cBars)
        Return result
    End Function

The GetEnabledMso method of the CommandBars object was introduced in Office 2007. It accepts the Id of the Ribbon control to be checked. That is, you find such a control in the Ribbon UI and then look for its ID in one of the following downloadables from the Microsoft web site: for Office 2007, see here; for Office 2010, see this page. Because GetEnabledMso refers to a Ribbon command, it doesn’t depend on the state or actual availability of any command bar or command bar control.

Note that with Add-in Express, you use the following function:

' Add-in Express version
    Private Function IsEditing() As Boolean
        Return Not Me.GetRibbonControlEnabled("FileNewDefault")
    End Function

The correct solution for all Excel versions, 2000 – 2010

Here is the function whose draft I published several times elsewhere:

' universal solution
    Function IsEditing() As Boolean
        If ExcelApp.Interactive = False Then Return False
        Try
            ExcelApp.Interactive = False
            ExcelApp.Interactive = True
        Catch
            Return True
        End Try
        Return False
    End Function

That is, you check the Interactive property of the Excel.Application object and set it to False. If this causes an exception, it means the user is editing a cell. If there’s no exception, you restore the Interactive. This is required because this MSDN article describes that property as follows:

True if Microsoft Excel is in interactive mode; this property is usually True. If you set the this property to False, Microsoft Excel will block all input from the keyboard and mouse (except input to dialog boxes that are displayed by your code). Read/write Boolean.

Blocking user input will prevent the user from interfering with the macro as it moves or activates Microsoft Excel objects.

If you set this property to False, don’t forget to set it back to True. Microsoft Excel won’t automatically set this property back to True when your macro stops running.

Good luck!

You may also be interesed in:

12 Comments

  • http://0.gravatar.com/avatar/ec554ccab0dfcd3cef4281dcf68a96a5?s=32&d=http%3A%2F%2F0.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G cesare says:

    Hi, I’ve been using the solution titled as “The correct solution for all Excel versions, 2000 – 2010″ for months. Finally I experienced the following “problem”: it returns true also when no cells are being editing but more then one sheet is contemporary selected. Is there a any way to fix it?
    Thank you in advance for helping me.

  • Hello Cesare,

    Thank you for pointing to this. At the moment I have no decent idea. You can find out how many sheets are selected via Windows.SelectedSheets but this doesn’t help in finding out if the user is editing a cell. Another idea would be to trace windows that Excel creates/activates when you edit a cell; implementing this in a simple function seems impossible. I need to sleep with this for a while. If I have any useful idea, I’ll post it here.

  • http://1.gravatar.com/avatar/b93f93fb51e3880febdbf2ac7bdf1691?s=32&d=http%3A%2F%2F1.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Sean says:

    Thank you for sharing your solution. I thought your code might help me in determining the time a user spends in edit mode. However I can’t seem to get this working in Excel 2003 (using the ‘Universal’ solution). I searched around and found out that:
    1) Try/Catch/Finally is not supported in VBA 6.X, so I have substituted “On Error” instead.
    2) ‘Return’ is not accepted by the compiler, so I have used IsEditing = False or IsEditing = True instead.
    3) The code seems to only be fired when I the function IsEditing is called/used. But I need to know the exact time when the user enters the edit mode (and leaves it). The worksheet_change event is only triggered after the cell has been entered. How can I make this code run independent of the user actions? (which seems to be the intent of the code in the first place)

    Thank you for your suggestions,

    Sean

  • Hello Sean,

    The solutions above cannot be used to achieve this. Even translated to VB6/VBA.

    You need to dwell into the Windows API world. The idea is to look for the creation/destruction of the window that Excel creates when you start/stop editing a cell. You can find out how that window is called using Spy++ (spyxx.exe). Spy++ is supplied with all Visual Studio versions even in VS6.

    Add-in Express uses Windows API as described above to provide events that occur before the user starts editing a cell and after the user finishes editing the cell.

  • http://0.gravatar.com/avatar/c6c8dbf4e876ce6fb4c398dc8a2a4195?s=32&d=http%3A%2F%2F0.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Stuart says:

    I’ve been using the general solution for a while now. I was stumped by a problem where the busy cursor was displayed over the Excel window while one of my add-in’s dialogs was displayed. Not terrible, but it’s confusing. I tracked the behavior to calling “IsEditing” while preparing the dialog for display.

    I changed the code to set the cursor and it works better now. My c# code:

    XlMousePointer mp = ExcelApp.Cursor;
    ExcelApp.Interactive = false;
    ExcelApp.Interactive = true;
    ExcelApp.Cursor = mp;

  • Wow! Just great! Thank you very much!

  • http://1.gravatar.com/avatar/39d9a1c35ec193f01eb80a6880ed1d06?s=32&d=http%3A%2F%2F1.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Aman Sharma says:

    Thanks

  • http://0.gravatar.com/avatar/2e04711d45cc96991561eef575f61c54?s=32&d=http%3A%2F%2F0.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G James K says:

    Thanks a lot.. I have a question. Is there any solution CTP is focused in edit mode?

  • Hello James,

    Sorry, I don’t understand the problem.

  • http://0.gravatar.com/avatar/2e04711d45cc96991561eef575f61c54?s=32&d=http%3A%2F%2F0.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G James K says:

    I made a Custom Task Pane (CTP) for navigating sheets in Excel with C#. It contains a listview control which shows up sheet names in excel file. And I can change active sheet by mouse double click. But, problem is that CTP cannot be focused in cell edit mode.

  • This looks like a by-design behavior of Excel: Microsoft don’t let you set focus to a custom task pane when a cell is being edited. I don’t think thre’s a solution for this.

  • http://0.gravatar.com/avatar/2e04711d45cc96991561eef575f61c54?s=32&d=http%3A%2F%2F0.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G James K says:

    Thanks for answer..

Post a comment

Have any questions? Ask us right now!