Click here to Skip to main content
12,503,681 members (53,700 online)
Click here to Skip to main content
Add your own
alternative version

Stats

22.9K views
527 downloads
22 bookmarked
Posted

Determining if Excel is in Edit mode with Win32 Interop

, 13 Apr 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
In this article, one can find a possible solution how to check or to be notified if the Excel Application is in Edit mode

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");
     // first get the container window for each workbooks
    IntPtr workbookContainer = Win32.FindWindowEx(new IntPtr( excelApp.Hwnd ), 
                                                  IntPtr.Zero, 
                                                  "XLDESK", String.Empty);
    // check if the search was positive
    if (workbookContainer != IntPtr.Zero)
    {   // continue with finding the editor window
        IntPtr editorWindow = Win32.FindWindowEx(workbookContainer, 
                                                 IntPtr.Zero,
                                                 "EXCEL6", String.Empty);
        // subclass it if the search was successful
        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)
    {
        // win32 message if style of the targetwindow has changed by any Win32 API call
        // WS_VISIBLE flag or any other
        case (int)Win32.WM.WM_STYLECHANGED:
            CheckWindowState();
            break;
        // win32 message if position of the targetwindow has changed by 
        // SetWindowPos API call
        // SWP_SHOWWINDOW for instance
        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()
{
    // check if the window now visible and also enabled
    if (Win32.IsWindowVisible(this.Handle) && Win32.IsWindowEnabled(this.Handle))
    {
        if (!_isEditorWindowActive)
        {   // change the state and raise event
            _isEditorWindowActive = true;
            OnRaiseSafetyEvent(_editModeOn, new object[] {this, EventArgs.Empty} );
        }
    }
    // check if the mode has changed back to non-edit mode
    else
    {
        if (_isEditorWindowActive)
        {   // change the state and raise event
            _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

License

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

Share

About the Author

Viktor Hamori
Software Developer (Senior)
Hungary Hungary
Office (Word, Excel) specialist
C#, COM, VC++, VB6
MFC, ATL,

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionGreat article, but how? Pin
otterpdp25-Jun-12 9:10
memberotterpdp25-Jun-12 9:10 
AnswerRe: Great article, but how? Pin
Viktor Hamori28-Aug-13 10:33
memberViktor Hamori28-Aug-13 10:33 
Generalanother edit mode case Pin
who_i25025-Apr-11 20:38
memberwho_i25025-Apr-11 20:38 
GeneralRe: another edit mode case Pin
Viktor Hamori26-Apr-11 3:38
memberViktor Hamori26-Apr-11 3:38 
QuestionEdit Directly in Cell Pin
engstrocity9-Apr-11 5:25
memberengstrocity9-Apr-11 5:25 
AnswerRe: Edit Directly in Cell Pin
Viktor Hamori26-Apr-11 3:35
memberViktor Hamori26-Apr-11 3:35 
GeneralRe: Edit Directly in Cell Pin
spcghst440@hotmail.com22-Oct-12 3:19
memberspcghst440@hotmail.com22-Oct-12 3:19 
GeneralRe: Edit Directly in Cell Pin
spcghst440@hotmail.com22-Oct-12 4:14
memberspcghst440@hotmail.com22-Oct-12 4:14 
Got it working. Here is the updated code if anyone else is interested:

Notes: the "EXCEL<" window never goes disabled/enabled. So I had to attach to different messages, and test for the option EditDirectlyInCell being true or not for binding to the handle and for testing the windows state.

using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Runtime.InteropServices;
 

namespace ExcelUtil
{
 

    /// <summary>
    /// Viktor Hamori 09/04/2009 - This class represents the EditMode status of Excel
    /// with safe subclassing the Excel cell editor window 
    /// http://www.codeproject.com/Articles/35398/Determining-if-Excel-is-in-Edit-mode-with-Win32-In
    /// </summary>
    public class ExcelEditorModeEventPublisher : NativeWindow, IDisposable
    {
        private bool _EditDirectlyInCell = false;
 
        private class Win32
        {
            public enum WM
            {
                WM_WINDOWPOSCHANGED = 0x0047,
                WM_STYLECHANGED = 0x007D,
                WM_SETFOCUS = 0x0007,
                WM_KILLFOCUS = 0x0008
            }
 
            [DllImport("user32.dll", CharSet = CharSet.Auto)]
            static public extern IntPtr FindWindowEx(IntPtr hWnd, IntPtr hChild, string strClassName, string strName);
            [DllImport("user32.dll", CharSet = CharSet.Auto)]
            static public extern bool IsWindowVisible(IntPtr hWnd);
            [DllImport("user32.dll", CharSet = CharSet.Auto)]
            static public extern bool IsWindowEnabled(IntPtr hWnd);
        }
 
        #region Declaration
        // disposing ...
        private object _lock = new object();
        private volatile bool _isDisposed = false;
        // editor window status
        private bool _isEditorWindowActive = false;
 
        // event declarations
        protected event EventHandler _editModeOn;
        protected event EventHandler _editModeOff;
        #endregion

        #region Constructor and Destructor
        /// <summary>
        /// Constructor accepting the editor window
        /// </summary>
        /// <param name="editorWindow"></param>
        internal ExcelEditorModeEventPublisher(IntPtr editorWindow)
        {
            if (editorWindow == IntPtr.Zero)
                return;
            InitObservation(editorWindow);
        }
        /// <summary>
        /// Constructor for finding the Editor Window in Excel window hierarchy
        /// </summary>
        /// <param name="excelApp"></param>
        internal ExcelEditorModeEventPublisher(Microsoft.Office.Interop.Excel.Application excelApp)
        {
 
            if (excelApp == null)
                throw new ArgumentNullException("excelApp");
 
            IntPtr editorWindow = IntPtr.Zero;
            _EditDirectlyInCell = excelApp.EditDirectlyInCell;
            if (!_EditDirectlyInCell)
            {
                editorWindow = Win32.FindWindowEx(new IntPtr(excelApp.Hwnd), IntPtr.Zero, "EXCEL<", String.Empty); //FindEditorHandle(excelApp.Hwnd, "XLMAIN", "EXCEL<");
                if (editorWindow != IntPtr.Zero)
                {
                    InitObservation(editorWindow);
                    return;
                }
            }
            else
            {
                editorWindow = FindEditorHandle(excelApp.Hwnd, "XLDESK", "EXCEL6");
                if (editorWindow != IntPtr.Zero)
                {
                    InitObservation(editorWindow);
                    return;
                }
            }
 

            throw new Exception("Unfortunatelly, Excel editor window cannot be detected!");
        }
 
        private IntPtr FindEditorHandle(int excelAppHwnd, string mainWindowName, string editorWindow)
        {
            IntPtr workbookContainer = Win32.FindWindowEx(new IntPtr(excelAppHwnd), IntPtr.Zero, mainWindowName, String.Empty);
            IntPtr editWindow = IntPtr.Zero;
 
            // check if the search was positive
            if (workbookContainer != IntPtr.Zero)
            {   // continue with finding the editor window
                editWindow = Win32.FindWindowEx(workbookContainer, IntPtr.Zero, editorWindow, String.Empty);
            }
            return editWindow;
        }
        /// <summary>
        /// Init the observer mechanism.
        /// </summary>
        private void InitObservation(IntPtr editorWindow)
        {
            AssignHandle(editorWindow);
            CheckWindowState();
        }
        /// <summary>
        /// Releasing the window in the destructor
        /// </summary>
        ~ExcelEditorModeEventPublisher()
        {
            Disposing();
        }
        /// <summary>
        /// The real dispose method
        /// </summary>
        protected void Disposing()
        {
            lock (_lock)
            {
                if (!_isDisposed)
                {
                    ReleaseHandle();
                    _isDisposed = true;
                }
            }
        }
        #region IDisposable Members
        /// <summary>
        /// Explicit clear up
        /// </summary>
        public void Dispose()
        {
            Disposing();
            GC.SuppressFinalize(this);
        }
        #endregion

        #endregion

        #region Window Procedure
        /// <summary>
        /// We have to observe the window changes (visibility, enabled state)
        /// </summary>
        /// <param name="m"></param>
        protected override void WndProc(ref Message m)
        {
            switch (m.Msg)
            {
                // win32 message if style of the targetwindow has changed by any Win32 API call
                // WS_VISIBLE flag or any other
                case (int)Win32.WM.WM_STYLECHANGED:
                    CheckWindowState();
                    break;
                // win32 message if position of the targetwindow has changed by SetWindowPos API call
                // SWP_SHOWWINDOW for instance
                case (int)Win32.WM.WM_WINDOWPOSCHANGED:
                    CheckWindowState();
                    break;
                case (int)Win32.WM.WM_SETFOCUS:
                    CheckWindowState(true);
                    break;
                case (int)Win32.WM.WM_KILLFOCUS:
                    CheckWindowState(false);
                    break;
            }
            base.WndProc(ref m);
        }
        #endregion

        #region Event handling
        /// <summary>
        /// Run the style/pos change checker ..
        /// <param name="isEnabled">This parameter only takes effect if _EditDirectlyInCell is true for the excel application.</param>
        /// </summary>
        private void CheckWindowState(bool isEnabled = false)
        {
            try
            {
                // check if the window now visible andalso enabled
                if (!_EditDirectlyInCell & isEnabled)
                {
                    if (!_isEditorWindowActive)
                    {   // change the state and raise event
                        _isEditorWindowActive = true;
                        OnRaiseSafetyEvent(_editModeOn, new object[] { this, EventArgs.Empty });
                    }
                }
                else if (!_EditDirectlyInCell & !isEnabled)
                {
                    if (_isEditorWindowActive)
                    {   // change the state and raise event
                        _isEditorWindowActive = false;
                        OnRaiseSafetyEvent(_editModeOff, new object[] { this, EventArgs.Empty });
                    }
                }
                else if (Win32.IsWindowVisible(this.Handle) && Win32.IsWindowEnabled(this.Handle))
                {
                    if (!_isEditorWindowActive)
                    {   // change the state and raise event
                        _isEditorWindowActive = true;
                        OnRaiseSafetyEvent(_editModeOn, new object[] { this, EventArgs.Empty });
                    }
                }
                // check if the mode has changed back to non-edit mode
                else
                {
                    if (_isEditorWindowActive)
                    {   // change the state and raise event
                        _isEditorWindowActive = false;
                        OnRaiseSafetyEvent(_editModeOff, new object[] { this, EventArgs.Empty });
                    }
                }
            }
            catch (Exception ex)
            {
                //really do not want the events crashing these messages
                Console.WriteLine("Eating exception: : " + ex.ToString());
            }
        }
        /// <summary>
        /// Protect the Excel message mechanism against client code!
        /// </summary>
        /// <param name="eventHandler"></param>
        /// <param name="args"></param>
        protected static void OnRaiseSafetyEvent(Delegate eventHandler, object[] args)
        {
            if (eventHandler == null)
                return;
            Delegate[] delegates = eventHandler.GetInvocationList();
            if (delegates != null)
            {
                foreach (Delegate del in delegates)
                {
                    try { del.DynamicInvoke(args); }
                    catch { }
                }
            }
        }
        /// <summary>
        /// Edit mode switched on event accessor
        /// </summary>
        public event EventHandler EditModeOn
        {
            add
            {
                _editModeOn += value;
            }
            remove
            {
                _editModeOn -= value;
            }
        }
 
        /// <summary>
        /// Edit mode switched on event accessor
        /// </summary>
        public event EventHandler EditModeOff
        {
            add
            {
                _editModeOff += value;
            }
            remove
            {
                _editModeOff -= value;
            }
        }
 

        #endregion

        #region Properties
        /// <summary>
        /// Property reflecting editor mode state
        /// </summary>
        public bool IsExcelInEditMode
        {
            get
            {
                return _isEditorWindowActive;
            }
        }
        #endregion
    }
}

GeneralRe: Edit Directly in Cell Pin
spcghst440@hotmail.com22-Oct-12 5:34
memberspcghst440@hotmail.com22-Oct-12 5:34 

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.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.160919.1 | Last Updated 13 Apr 2009
Article Copyright 2009 by Viktor Hamori
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid