Trapping a Shape Change Event

**yosemitejohn** · 04-24-2012, 07:42 PM

Say I have a bunch of shapes on a sheet that were created and positioned using VBA. Then say I want to resize one of the shapes or perhaps move it. How do I trap this so I can record the new location and size of the shape. I've been fiddling with the Worksheet Change event with no luck...

Regards, John

**shg** · 04-24-2012, 07:49 PM

There is no event when you resize or change a shape.

**yosemitejohn** · 04-24-2012, 10:13 PM

OK--is there a work-around? The idea is to trigger code to write the changed coordinates to a cell in the spreadsheet...

**shg** · 04-25-2012, 12:21 AM

A work-around like what?

**Andy Pope** · 04-25-2012, 04:45 AM

If you use the selection change event of the worksheet you could then check ALL shapes on the sheet to see if anything changed.
Of course you will only be able to check once the user has finished adjusting shape(s) and actually selects a different cell on the worksheet.

Assign this macro to shapes.

code module

Public g_shpTemp As Shape

Sub ShapeChange()
    
    Set g_shpTemp = ActiveSheet.Shapes(Application.Caller)
    g_shpTemp.Select
    
End Sub

Thisworkbook object

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    If Not g_shpTemp Is Nothing Then
        MsgBox "Check Shape " & g_shpTemp.Name & " on " & g_shpTemp.Parent.Name
        
        ' do something here
        
        Set g_shpTemp = Nothing
    End If
    
End Sub

You will get the hand cursor when selecting the shape but then the sizing anchors will appear. The detail of which shape will appear when the worksheet cell next changes.