VBA delete freeform positioned over chartobject

[fboehlandt]

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
    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...

 

[Case_Germany]

Hi,

Is the chartobject rather than spreadsheet the parent object of the freeform?

Yes. The chart also has "shapes" when it was activated while you insert the freeform.

Try it like this:

Option Explicit
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 = msoChart Then
                If Sh.Chart.Shapes.Count > 0 Then
                    If Sh.Chart.Shapes.Item(1).Type = msoFreeform Then
                        Sh.Chart.Shapes.Item(1).Delete
                    End If
                End If
            ElseIf Sh.Type = msoFreeform Then
                Sh.Delete
            End If
    '       alternatively If Not (Sh.Type = msoChart Or Sh.Type = msoFormControl) Then: Sh.Delete
        Next Sh
    End With
End Sub

 

[fboehlandt]

Thank you!! This has kept me busy for hours. I just didn't know how to reference the freeform within the chart

Sh.Chart.Shapes.Item(1).Type

In addition, Excel 2007 does not allow for recording changes to shapes anymore. A seemingly simple problem that, unfortunately, cost me a lot of time. Anyway, thanks for your help again...