Andrei Smolin

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:

Custom events of Excel shapes
Event Description
ShapesSelectionChange Occurs when the user selects or deselects a shape(s)
ShapesBeforeGroup Occurs before selected shapes are grouped
ShapesBeforeUngroup Occurs before selected shapes are ungrouped
ShapesRemoved Occurs when a shape(s) is removed from the Shapes collection of the current sheet
ShapesCreated Occurs when a shape(s) is added to the Shapes collection of the current sheet
ShapesGrouped Occurs when a group of shapes is created
ShapesUngrouped Occurs when a group of shapes is ungrouped
ShapeMoved Occurs when a shape is moved
ShapeResized Occurs when a shape is resized
ShapeRenamed Occurs when a shape is renamed

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.

An add-in logging shapes events

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)

12 Comments

  • https://secure.gravatar.com/avatar/1536f692160ba5c9f393a82f7c8be505?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G rosh says:

    hi i would like to know how can i re-color a shape throught .net code

  • https://secure.gravatar.com/avatar/ab4ec2858cfdf1e44dadf8c50fae314d?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Dmitry Kostochko (Add-in Express Team) says:

    Hi Rosh,

    You can use the ShapeStyle and BackgroundStyle properties of the Shape object. Please see the following MSDN articles for details:
    http://msdn.microsoft.com/en-us/library/office/bb240883(v=office.12)
    http://msdn.microsoft.com/en-us/library/office/bb240911(v=office.12)

    Please note, these properties were introduced in the Excel 2007 Object Model.

  • https://secure.gravatar.com/avatar/a576b549ac4599bf214ab7e46ef52fc3?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Rusell says:

    Hi

    How do I use the event to trigger a custom macro. For instance i would like a message box to appear with the new co-ordinates when one of the shape events are triggered.

  • https://secure.gravatar.com/avatar/29957f26ad2d8ba527fd9cc8cfa7b2e0?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Andrei Smolin (Add-in Express Team) says:

    You need to run that macro via ExcelApp.Run(…). Note that the macro must be public and it must locate in a standard module.

  • https://secure.gravatar.com/avatar/363572167f10a869ea36411d1a2a64aa?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G hengchengfei says:

    why throw exception when i select a shape in the follow.

    selectedShapeRangeObj = selection.GetType().InvokeMember(“ShapeRange”, System.Reflection.BindingFlags.GetProperty, null, selection, null);

  • https://secure.gravatar.com/avatar/29957f26ad2d8ba527fd9cc8cfa7b2e0?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Andrei Smolin (Add-in Express Team) says:

    Hello hengchengfei,

    Is this the case described above under the subtitle Excel 2002 – 2003 issue? If not, can you please send me that workbook or any other workbook with the shape on which the issue is reproducible? You can use our support email address, see {Add-in Express installation folder}\readme.txt. Please specify the shape producing the issue; Excel version, service pack and bitness; Add-in Express version used; exception details.

  • https://secure.gravatar.com/avatar/ef129cb44f5e292dafa72cdc34d44658?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G HeinrichMS says:

    How about PowerPoint shapes ? Is it possible to determine which shape has been selected ?

  • https://secure.gravatar.com/avatar/29957f26ad2d8ba527fd9cc8cfa7b2e0?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Andrei Smolin (Add-in Express Team) says:

    Hello Heinrich,

    I suppose yes. Look at these properties of the Selection object in PP 2013: ShapeRange, ChildShapeRange, HasChileShapeRange. Nevertheless, this requires studying details. I would suggest using this approach: use Microsoft.VisualBasic.Information.TypeName() to get the type name behind items listed in Selection.ShapeRange when a shape(s) is selected. With this knowledge, you can check the selection in the Commandbars.OnUpdate event; in Add-in Express this event is mapped to the CommandBarsUpdate event of the ADXPowerPointAppEvents component.

  • https://secure.gravatar.com/avatar/5a83fc75899323760c5c96bca7d3dc3b?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G raja says:

    hi…i want to find the type name of a shape in excel using C# ,without using selection .Here i have shape and object id.With this references ,is it possible to find out the typename??

  • https://secure.gravatar.com/avatar/29957f26ad2d8ba527fd9cc8cfa7b2e0?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Andrei Smolin (Add-in Express Team) says:

    Hello Raja,

    Microsoft.VisualBasic.Information.TypeName(obj) returns the type name of the COM object supplied. Hope this helps.

  • https://secure.gravatar.com/avatar/0b8ce75844147ad14cdeb897b1afee8e?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Bert says:

    I would like to know if it is possible to make such an add-in exclusively available with a specific workbook only. Something imbedded in the workbook?

    Thanks

  • https://secure.gravatar.com/avatar/29957f26ad2d8ba527fd9cc8cfa7b2e0?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Andrei Smolin (Add-in Express Team) says:

    Hello Bert,

    The workbook should have something identifiable, say, a custom property, see https://msdn.microsoft.com/en-us/library/office/ff834990(v=office.15).aspx. In this case, the add-in can turn its functionality on for that workbook.

Post a comment

Have any questions? Ask us right now!