Delete multiple chartobjects from one chart sheet

[userkjay]

The objective: Use VBA to delete multiple chartobjects from one chart sheet.

If multiple chartobjects are created on one chart using the following code:

[FONT=Arial]Sub ChartCreate()[/FONT]
[FONT=Arial]Dim chtTemp  As ChartObject[/FONT]
[FONT=Arial]Charts.Add.Location Where:=xlLocationAsNewSheet, Name:="My Chart"[/FONT]
[FONT=Arial]For j = 1 To 3[/FONT]
[FONT=Arial]   Set chtTemp = Charts("My Chart").ChartObjects.Add(4 ^ j, 4 ^ j, 10, 10)[/FONT]
[FONT=Arial]Next[/FONT]
[FONT=Arial]MsgBox Charts("My Chart").ChartObjects.Count[/FONT]
[FONT=Arial]End Sub[/FONT]

The 3 chartobjects on the chart “My Chart” can be selected with the mouse and deleted by hitting the delete key. I can not duplicate this using VBA. I have tried the macro recorder, but it does not capture the delete command. I have tried using sendkeys, with no success. One of the many methods of VBA code that I have tried is below:

[FONT=Arial]Sub ChartDelete()[/FONT]
[FONT=Arial]For i = 1 To ActiveChart.ChartObjects.Count[/FONT]
[FONT=Arial]   ActiveChart.ChartObjects(ActiveChart.ChartObjects(i).Name).Delete[/FONT]
[FONT=Arial]Next[/FONT]
[FONT=Arial]End Sub[/FONT]

If anyone knows of a work around it would be greatly appreciated. I have found this problem to be a challenge.

 

[pgc01]

pgc01, both of the above methods produce an error.

Hi

Sorry, I tested yesterday in excel 2000 and it works.

I tested now in excel 2010 and it does not work. I'll ask around and if I have news I'll post them.

 

[userkjay]

Hi

Sorry, I tested yesterday in excel 2000 and it works.

I tested now in excel 2010 and it does not work. I'll ask around and if I have news I'll post them.

Thanks for asking. Any help would be outstanding.

 

[ZVI]

Hi, try this:

Sub ChartObjectsDel()
  Dim i As Long, ver As Long
  ver = Val(Application.Version)
  With Sheets("My Chart").ChartObjects
    For i = .Count To 1 Step -1
      If ver > 11 Then
        .Item(i).ShapeRange.Delete
      Else
        .Item(i).Delete
      End If
    Next
  End With
End Sub

Regards,

 

[userkjay]

Hi, try this:

Sub ChartObjectsDel()
 Dim i As Long, ver As Long
 ver = Val(Application.Version)
 With Sheets("My Chart").ChartObjects
   For i = .Count To 1 Step -1
     If ver > 11 Then
       .Item(i).ShapeRange.Delete
     Else
       .Item(i).Delete
     End If
   Next
 End With
End Sub

Regards,

ZVI, I tried your suggested method, but I recieve an error.

 

[ZVI]

Yea, previous code was for Excel 2003/2007.
Not sure this is good idea, but the code below works for Excel 2010 as well.

Sub ChartObjectsDelete()
  Dim i As Long, ver As Long, tmp As String
  ver = Val(Application.Version)
  Application.ScreenUpdating = False
  If ver >= 14 Then
    With Sheets.Add
      tmp = .Name
    End With
  End If
  With Sheets("My Chart").ChartObjects
    For i = .Count To 1 Step -1
      If ver < 12 Then
        .Item(i).Delete
      ElseIf ver = 12 Then
        .Item(i).ShapeRange.Delete
      Else  ' ver = 14
        .Item(i).Chart.Location Where:=xlLocationAsObject, Name:=tmp
      End If
    Next
  End With
  With Application
    If ver >= 14 Then
      .DisplayAlerts = False
      Sheets(tmp).Delete
      .DisplayAlerts = True
    End If
    .ScreenUpdating = True
  End With
End Sub

Regards,

 

[pgc01]

Hi Vladimir, nice to see you.

I tried the code and it did work.

I think that by now we can assume that there is really a problem with the ChartObjects collection of the Chart Sheet.

 

[ZVI]

Hi Vladimir, nice to see you.

I tried the code and it did work.

I think that by now we can assume that there is really a problem with the ChartObjects collection of the Chart Sheet.

Hi Pedro, it’s always pleasant to be in the good company here
Thank you for the confirmation. Yea, the issue really looks like the bug.
I will prepare & send the ChartObjects issue report to Microsoft.
Vlad

 

[userkjay]

Yea, previous code was for Excel 2003/2007.
Not sure this is good idea, but the code below works for Excel 2010 as well.

Sub ChartObjectsDelete()
Dim i As Long, ver As Long, tmp As String
ver = Val(Application.Version)
Application.ScreenUpdating = False
If ver >= 14 Then
 With Sheets.Add
   tmp = .Name
 End With
End If
With Sheets("My Chart").ChartObjects
 For i = .Count To 1 Step -1
   If ver < 12 Then
     .Item(i).Delete
   ElseIf ver = 12 Then
     .Item(i).ShapeRange.Delete
   Else  ' ver = 14
     .Item(i).Chart.Location Where:=xlLocationAsObject, Name:=tmp
   End If
 Next
End With
With Application
 If ver >= 14 Then
   .DisplayAlerts = False
   Sheets(tmp).Delete
   .DisplayAlerts = True
 End If
 .ScreenUpdating = True
End With
End Sub

Regards,

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Wow. Thank you very much. You don't know how long I spent on this problem.

<o></o>
Basically the code is taking the chartobject, creating a new chart, and then deleting the new chart location. Is this correct?<o></o>

 

[ZVI]

Basically the code is taking the chartobject, creating a new chart, and then deleting the new chart location. Is this correct?

It's almost, for Excel 2010 the code creates new temporary sheet (not the Chart sheet), then moves ChartObjects to that sheet and then deletes the temporary sheet.

 

[userkjay]

It's almost, for Excel 2010 the code creates new temporary sheet (not the Chart sheet), then moves ChartObjects to that sheet and then deletes the temporary sheet.

Amazing. That is some out of the box thinking. Thanks again. I was literally having dreams (you could call them nightmares) where I was coding to fix this problem. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>