PDA

View Full Version : Solved: Delete freeform positioned over chartobject



fboehlandt
09-22-2011, 01:34 AM
Hello everyone,
I have a strange problem with which I am making no headway. I would like to delete a freeform from a particular spreadsheet. The code below does exactly that whilst retaining all other shape types:



Sub dshape()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Sheets("Response_Q2")
Dim Sh As Shape
With ws
For Each Sh In .Shapes
If Sh.Type = msoFreeform Then: Sh.Delete
' alternatively: If Not (Sh.Type = msoChart Or Sh.Type = msoFormControl) Then: Sh.Delete
' alternatively: If shp.AutoShapeType = 138 Then: Sh.Delete
Next Sh
End With
End Sub



However, if the freeform is positioned on top of a chart (i.e. the chart was selected, the freeform pasted into the chart area/plot area), the code does not recognize the freeform. I assume it is to do with the freeform now being regarded as 'part' of the chart? Is the chartobject rather than spreadsheet the parent object of the freeform? If so, how is the code above to be altered to accomodate such? I am really at a loss here and would greatly appreciate any help...

p45cal
09-22-2011, 03:42 AM
Convoluted, but try:
Sub dshape()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Sheets("Response_Q2")
Dim Sh As Shape
With ws
For Each Sh In .Shapes
If Sh.Type = msoFreeform Then Sh.Delete
If Sh.Type = msoChart Then
For Each shp In Sh.OLEFormat.Object.Chart.Shapes
If shp.Type = msoFreeform Then shp.Delete
Next shp
End If
' alternatively: If Not (Sh.Type = msoChart Or Sh.Type = msoFormControl) Then: Sh.Delete
' alternatively: If shp.AutoShapeType = 138 Then: Sh.Delete
Next Sh
End With
End Sub

fboehlandt
09-22-2011, 04:29 AM
:) Many thanks, this works fine. Since macro recording in Excel 2007 is disabled for shapes and chartobjects I wasn't sure how to reference the freeform.