Excel shapes events: getting notified about user actions

The Excel object model doesn’t have any events to control manipulations with shapes. In this blog I demonstrate an approach to solving this problem.

The sample add-in project available for download at the end of the post creates the custom events listed below. Note that individual shapes within a group aren't handled in this add-in. This isn't a limitation of the approach: this is a restriction of this add-in only (nevertheless, pay attention to an Excel 2002 – 2003 issue described in How to determine if the object selected in Excel is a shape?). So, the events for shapes are as follows:

How to use the add-in?

The add-in adds a button and an Advanced Task Pane. Click the button to start logging events from Excel shapes; log records are shown in the pane.

How to determine if the object selected in Excel is a shape?

Because Excel.Selection can return almost any type from the Excel type library, you need to check the type of the selected object.

So, I’ve started with printing the type name of the object returned by ExcelApp.Selection (in the code of the add-in, see the method ParseSelection()).

C#:

string typeName = Microsoft.VisualBasic.Information.TypeName(selection);
Log.Output("The type name of the selected object is " + typeName);

Studying the debug output reveals the fact that Excel provides many classes representing geometrical figures and controls: Rectangle, Oval, Arc, Button, Label, etc. Every such class provides the ShapeRange property. This property returns an Excel.ShapeRange object representing a set of Excel shapes i.e. of Excel.Shape objects. When you select e.g. a rectangle, Application.Selection returns an Excel.Rectangle. To get an Excel.Shape corresponding to the selected rectangle, you call Rectangle.ShapeRange and then ShapeRange[i] (in VB.NET , call ShapeRange.Items(i)).

Useful note. If you look into the properties that ShapeRange provides, you’ll find that it contains properties for a set of shapes and for just one shape. For instance, calling ShapeRange.Name produces an exception if ShapeRange refers to more than one shape.

Excel 2002 – 2003 issue. In Excel 2002 – 2010, the user can select a shape within a group of shapes. In Excel 2002 – 2003 however, calling the ShapeRange property of a shape selected in this way, produces an exception. That means you cannot use the mechanism described above to determine if the selected object is a shape. And even if it is possible to check the type of the selected object (say, Rectangle or Oval) the impossibility to get ShapeRange leads to impossibility to check if the selected object belongs to a group.

How to identify shapes?

You cannot use Shape.Name to identify shapes: renaming an existing shape and then copying/pasting it creates duplicated shape names.

Now look at the property Shape.ID. The description of this property is utterly unintelligible:

Returns the type for the specified object.

It obviously contradicts the property name. Yet people regard Shape.ID as a unique identifier (see here and here) and I use this property to identify shapes (not shape types) in this add-in. But this property is missing in Excel 2000. So, in this add-in, Excel 2000 is not supported.

How to detect selection changes?

The best moment to check if the selection changes is the Commandbars.OnUpdate event. In an old document, they said:

The CommandBars collection supports the OnUpdate event, which is triggered in response to changes made to a Microsoft® Office document that might affect the state of any visible command bar or command bar control. For example, the OnUpdate event occurs when a user changes the selection in an Office document.

That is, to determine if the user selects the same or some other shape, you need to compare the ID of the selected shape(s) with the ID of the shape selected in the previous Commandbars.OnUpdate event.

Note that Excel provides the Worksheet.SelectionChange event; it occurs only when the selected object is a range of cells.

In the code, see how the ShapesSelectionChange method is invoked.

How to detect moving, sizing or renaming a shape?

The add-in implements the algorithm below:

• At startup, gather all required information about existing shapes: the ID of the corresponding Excel.Shape object, shape name, location, size, etc.
• Handle Commandbars.OnUpdate to determine if one of the shapes is selected (see How to detect selection changes? and How to identify shapes?)
• Compare the location and size of the selected shape(s) with the information about this shape stored in the previous Commandbars.OnUpdate event.
• If these data differ, run your code handling this situation.
• Store the new location/size of the shape.

In the code, see how the methods ShapesShapeMoved, ShapesShapeResized and ShapesShapeRenamed are invoked.

How to detect creating/deleting a shape?

In the Commandbars.OnUpdate event, you compare two sets of shapes: the shapes existing now and those existing in the previous run of the Commandbars.OnUpdate event.

In the code, see how the methods ShapesCreated and ShapesRemoved are invoked.

How to detect grouping/ungrouping shapes?

The add-in demonstrates getting events that occur before and after the user groups/ungroups shapes.

To get the “before” events in Excel 2002 – 2003, you intercept clicking the CommandBar buttons ID=164 (Group) and ID=165 (Ungroup). To do the same in Excel 2007-2010, you intercept invoking the Ribbon commands IdMso=”ObjectsGroup” and IdMso=”ObjectsUngroup”. To intercept these commands in these Excel versions, I use two Built-in CommandBar Control Connectors and two Ribbon Command components. Note that the "before" events are cancellable.

As to the “after” events, see how the methods ShapesGrouped and ShapesUngrouped are invoked.

Good luck!

Available downloads:

These sample COM add-in was developed using Add-in Express for Office and .net:

COM add-in installer (with C# source code)