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!
23 Comments
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.
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.
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!
Thanks
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.
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.
Thanks for answer..
[…] Thanks to Andrei Smolin for posting the vba version of this code in his blog. […]
how can i use “Lock Drawing mode” from within VSTO using C#/VB.net ?
is there any way to implement “Lock Drawing mode” in VSTO 4.0?
Thank you,
Fawad Ali
Hello Fawad,
None of the Office object models provides access to that functionality.
[…] Thanks to Andrei Smolin for posting the vba version of this code in his blog. […]
This solution is quite old, so I’m not sure if this has always been the case, but in many cases, calling `xlApp.Interactive = false` will cause even more issues. I discovered that in Excel 2013/2016 this line of code will *interrupt* the user if they happen to be performing a number of actions:
– Resizing or moving an excel-owned window (the action will be aborted)
– In a non-modal excel-owned dialog like the VBA editor (they will be ‘kicked’ back to the main workbook window)
– Selecting or moving cells (really, any mouse-drag action) will be aborted.
Looks like it’s back to the drawing board to find an unobtrusive way of detecting if the user is busy.
Hello Alain,
I suggest that you try to use GetEnabledMso() with Excel 2013-2016.
Hey all, just came across this. Tweaked the universal solution, and the below seems to work for me. If there are any obvious issues with it please do let me know, otherwise hope this helps.
Function IsEditing() As Boolean
On Error GoTo Exception
‘Has no actual effect, but will cause exception if user is editing.
Application.Interactive = Application.Interactive
‘Otherwise, IsEditing = false.
IsEditing = False
Exit Function
Exception:
IsEditing = True
End Function
Hello Daniel,
Thank s for the idea!
Hi Daniel,
This “elegant” solution also worked for me.
I’ve been struggling with a Private Sub Worksheet_SelectionChange(ByVal Target As Range) routine intercepting data in edit mode and your suggestion was a good solution.
Thanks!
This worked a treat. I was trying to use the Application.Ready property, but it was not nearly as reliable as the above! Thanks :)
[…] Thanks to Andrei Smolin for posting the vba version of this code in his blog. […]