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?
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.