Introduction
Before working out any solution on our own, the web is often checked first to find whether an official solution already exists.
Regarding this issue everyone can find a couple of articles, community forum chats with lots of ideas on how to workaround the lack of Excel COM API determining if Excel application itself is in edit mode.
You can check the history here.
Background
What does edit mode really mean? When the user double clicks or starts typing over a cell, presses F2 key or uses the editor bar below further toolbars Excel starts its edit mode.
When edit mode is on, couple of menu items and functionalities alongwith do not work indicating that the application is in Edit mode (menu items get disabled).
It is the same effect if anyone writes a macro in Visual Basic for Application module and wants to start it.
Basically it is fine but if you would like to write an addin code module it is no longer true, calling some built-in functionalities from addin code causes Exceptions or your custom menu, toolbar items are still enabled and not in synch with built-in toolbar items.
There is no event or API property for you to indicate that Excel stepped in edit mode making you capable of disabling your custom stuff in time.
Even the Application.Isready
property is not always working and polling a property from a different thread or by a timer is not the best approach.
For this reason, what we need is an event raised at that time when edit mode gets on or off. In addition to this, a property to give information about whether Excel is currently in edit mode and to be retrievable at any time from your code can also be useful.
Using the Code
Event provider is defined for these purposes:
public class ExcelEditorModeEventPublisher : NativeWindow, IDisposable
{
with its event accessors:
public event EventHandler EditModeOn
{
public event EventHandler EditModeOff
{
and property:
public bool IsExcelInEditMode
{
The possible solution can be to observe Excel's editor window (class = "Excel6"
) to see whether its style has changed.
Activating the Editor window will be done by calling certain Windows 32 API calls like ShowWindow, SetWindowPos, or style changes inside of Excel SetWindowLong.
These API calls generate particular windows messages that can be detected relatively easily. To be able to do that, we need to hook on Editor window by Subclassing or Hooking it. I have chosen subclassing now as .NET provides a Native window class for safe subclassing. You can freely change this approach to using hooks by SetWindowsHookEx.
Before starting the observation, the Editor window should be found in the constructor:
internal ExcelEditorModeEventPublisher
(Microsoft.Office.Interop.Excel.Application excelApp)
{
if (excelApp == null)
throw new ArgumentNullException("excelApp");
IntPtr workbookContainer = Win32.FindWindowEx(new IntPtr( excelApp.Hwnd ),
IntPtr.Zero,
"XLDESK", String.Empty);
if (workbookContainer != IntPtr.Zero)
{
IntPtr editorWindow = Win32.FindWindowEx(workbookContainer,
IntPtr.Zero,
"EXCEL6", String.Empty);
if (editorWindow != IntPtr.Zero)
{
InitObservation(editorWindow);
return;
}
}
throw new Exception("Unfortunately, Excel editor window cannot be detected!");
}
And now we are ready to start observation via target window handle:
private void InitObservation(IntPtr editorWindow)
{
AssignHandle(editorWindow);
CheckWindowState();
}
We are getting all messages sent to the Editor window first before it could process them throughout Window Procedure.
protected override void WndProc(ref Message m)
{
switch (m.Msg)
{
case (int)Win32.WM.WM_STYLECHANGED:
CheckWindowState();
break;
case (int)Win32.WM.WM_WINDOWPOSCHANGED:
CheckWindowState();
break;
}
base.WndProc(ref m);
}
Therefore we are capable of notifying our listeners about activation and deactivation of the target Editor window.
private void CheckWindowState()
{
if (Win32.IsWindowVisible(this.Handle) && Win32.IsWindowEnabled(this.Handle))
{
if (!_isEditorWindowActive)
{
_isEditorWindowActive = true;
OnRaiseSafetyEvent(_editModeOn, new object[] {this, EventArgs.Empty} );
}
}
else
{
if (_isEditorWindowActive)
{
_isEditorWindowActive = false;
OnRaiseSafetyEvent( _editModeOff, new object[]{this, EventArgs.Empty} );
}
}
}
History
- Viktor Hamori 13/04/2009 - 2. Version added protection for Excel message handler if the client subscriber fails (throws exception) for any reason