Assigning a “macro” to a Textbox in VSTO/C#

Posted on by David E. Craig

In Excel you have the ability to insert a textbox into your spreadsheet, right-click on it and then assign a macro. This can be handy because maybe you want to capture the user clicking on the textbox as to take a specific action. See below:

textbox macro

However, there is no way to do this from C#. I have seen a couple of solutions now where it calls for an XLAM (Excel Add-in) to be placed in the XLSTART folder that can be used to register these textbox clicks and then call into your VSTO DLL through COM.. a la early-binding.

Messy… Confused smile

One thing I tell customers all the time is to not mix and match VBA and VSTO. Choose one or the other. This is because you can get into some real sticky situations that are downright impossible to debug.

So, in order to get one customer off this VBA crutch and move their entire code base into VSTO/C#, I did the following:

  1. Created a Mouse Hook class that captures mouse events
  2. When a left mouse click is detected it fires an event that the caller hooks to.
  3. Once the event is tripped, it checks the Excel selection and ask the selection for it’s “name.”

NOTE: This property is not normally exposed in VSTO (for some reason). However, it is accessible via VBA so I know it is there. Therefore, to get at it, I have to pInvoke it from Excel.

Here is the code to the Mouse Event Handler class:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Runtime.InteropServices;
using System.Windows.Forms;
using System.Reflection;

public class UserActivityHook
{
    private static int hMouseHook;
    private delegate int HookProc(int nCode, int wParam, IntPtr lParam);
    private HookProc MouseHookProcedure;
    public delegate void MouseEventHandler(object sender, MouseEventArgs e);
    public event MouseEventHandler OnMouseActivity;

    #region mouse constants
    const int HC_ACTION = 0;
    const int WH_MOUSE_LL = 14;
    const uint WM_MOUSEMOVE = 0x200;
    const uint WM_LBUTTONDOWN = 0x201;
    const uint WM_LBUTTONUP = 0x202;
    const uint WM_LBUTTONDBLCLK = 0x203;
    const uint WM_RBUTTONDOWN = 0x204;
    const uint WM_RBUTTONUP = 0x205;
    const uint WM_RBUTTONDBLCLK = 0x206;
    const uint WM_MBUTTONDOWN = 0x207;
    const uint WM_MBUTTONUP = 0x208;
    const uint WM_MBUTTONDBLCLK = 0x209;
    const uint WM_MOUSEWHEEL = 0x20A;
    const uint WM_MOUSEHWHEEL = 0x20E;

    #endregion

    #region "DLL imports"
    // Methods
    [DllImport("user32.dll")]
    private static extern int CallNextHookEx(int idHook, int nCode,
                                           int wParam, IntPtr lParam);

    [DllImport("user32.dll")]
    private static extern int SetWindowsHookEx(int idHook, HookProc lpfn,
                                        IntPtr hInstance, int threadId);

    [DllImport("user32.dll")]
    private static extern bool UnhookWindowsHookEx(int idHook);
    #endregion

    #region "Structures"
    [StructLayout(LayoutKind.Sequential)]
    public class MouseHookStruct
    {
        // Fields
        public int dwExtraInfo;
        public int hwnd;
        public POINT pt;
        public int wHitTestCode;
    }

    [StructLayout(LayoutKind.Sequential)]
    public class POINT
    {
        // Fields
        public int x;
        public int y;
    }
    #endregion

    /// <summary>
    /// Start hook upon initialization
    /// </summary>
    public UserActivityHook()
    {
        Start();
    }

    /// <summary>
    /// Remove the hook on close.
    /// </summary>
    ~UserActivityHook()
    {
        Stop();
    }

    /// <summary>
    /// Starts the hook for the mouse events
    /// </summary>
    private void Start()
    {
        try
        {
            if (hMouseHook == 0)
            {
                // setup a callback for the WinAPI to this thread
                // instance which is shared by Excel, therefore
                // we are hooking to the message pump for Excel
                // but specificaly looking for mouse events.
                // See the callback:
                //   MouseHookProc()
                //
                MouseHookProcedure = new HookProc(MouseHookProc);
                hMouseHook = SetWindowsHookEx(14, MouseHookProcedure, IntPtr.Zero, 0);
                // hook failed for some reason…
                if (hMouseHook == 0)
                {
                    this.Stop();
                    throw new Exception("SetWindowsHookEx failed.");
                }
            }
        }
        catch (Exception ex)
        {
            throw new Exception("SetWindowsHookEx failed: " + ex.ToString());
        }
    }

    /// <summary>
    /// Is called by Windows when a Mouse event occurs.
    /// This is the main hook procedure setup from the
    /// Start() function.
    /// </summary>
    /// <param name="nCode"></param>
    /// <param name="wParam"></param>
    /// <param name="lParam"></param>
    /// <returns></returns>
    public int MouseHookProc(int nCode, int wParam, IntPtr lParam)
    {
        try
        {
            // is there a code passed?
            // and is the caller hooked to our event
            if (nCode >= 0 && OnMouseActivity != null)
            {
                MouseButtons buttons1 = MouseButtons.None;
                // is it a mouse event
                if (wParam == WM_LBUTTONUP)
                {
                    buttons1 = MouseButtons.Left;
                }
                else if (wParam == WM_RBUTTONUP)
                {
                    buttons1 = MouseButtons.Right;
                }
                else
                {
                    buttons1 = MouseButtons.None;
                }

                // extract the mouse params/struct passed
                // from windows so we can invoke it
                int num1 = 1;
                MouseHookStruct struct1 =
                    (MouseHookStruct)Marshal.PtrToStructure(lParam, typeof(MouseHookStruct));
                // cast into Mouse args and then invoke the event
                MouseEventArgs args1 = new MouseEventArgs(buttons1,
                                    num1, struct1.pt.x, struct1.pt.y, 0);
                OnMouseActivity.Invoke(this, args1);
            }
            return CallNextHookEx(hMouseHook, nCode, wParam, lParam);
        }
        catch
        {
            return 0; // fail silently here
        }
    }

    /// <summary>
    /// Turn off the hook.
    /// </summary>
    private void Stop()
    {
        try
        {
            bool flag = true;
            if (UserActivityHook.hMouseHook != 0)
            {
                // call API to unhook
                flag = UnhookWindowsHookEx(hMouseHook);
                hMouseHook = 0;
            }
            if (!flag)
            {
                throw new Exception("UnhookWindowsHookEx failed.");
            }
        }
        catch { } // ignore on fail
    }
}

To attach to the hook:

void hook_OnMouseActivity(object sender, System.Windows.Forms.MouseEventArgs e)
{
    try
    {
        if (e.Button == MouseButtons.Left)
        {
            object sel = Application.ActiveWindow.Selection;
            // we must pInvoke Excel to get the name property
            // from the selection since it is not exposed in
            // the PIA's…
            object Name = sel.GetType().InvokeMember("Name",
                    BindingFlags.GetProperty, null, sel, null);
            // cheap way to see if it is a textbox…
            if (Name.ToString().ToLower().Contains("text"))
            {
                MessageBox.Show("Got it: " + Name.ToString());
            }
        }
    }
    catch { }
}

Then to catch the event and determine what was clicked:

There it all is. Now the real beauty is that you can actually use this code to detect the user clicking on all sorts of things, not just a textbox. Winking smile

4 thoughts on “Assigning a “macro” to a Textbox in VSTO/C#”

  1. Hi Dave

    To handle other Mouse events like MouseMove and Mousewheel, do you use the same code structure?

    public event MouseEventHandler OnMouseMove;

    if (wParam == WM_MOUSEMOVE)
    {

    int num1 = 1;
    MouseHookStruct struct1 =
    (MouseHookStruct)Marshal.PtrToStructure(lParam, typeof(MouseHookStruct));

    MouseEventArgs args1 = new MouseEventArgs(null,
    num1, struct1.pt.x, struct1.pt.y, 0);
    OnMouseMove.Invoke(this, args1);
    }

    void hook_OnMouseMove(object sender, System.Windows.Forms.MouseEventArgs e)
    {
    }

    Also, is there another piece of code to specify that OnMouseActivity.Invoke(this, args1); calls hook_OnMouseActivity?

    Andrew

    1. I have not tested using Wheel or Move, but this methodology you outline looks sound. I am not sure if the callback is exactly the same, since the Wheel move would likely have some value indicating direction and amount, but I am sure that can be found on MSDN.

  2. Hey would you mind sharing which blog platform you’re working with?
    I’m planning to start my own blog in the near future but I’m having a tough time deciding between BlogEngine/Wordpress/B2evolution and Drupal.

    The reason I ask is because your design seems different then most blogs and I’m
    looking for something unique. P.S Apologies for
    being off-topic but I had to ask!

Leave a Reply