Deleting a Chart in VBA

[ActuarySpaz]

Hi! This is my very first post here, so hello everyone!

I am writing a marco that creates a chart everytime it is run, however, I want the previous chart to be deleted before the new one is created. Right now, each chart is just layered over one another. I tried recording a macro to see the code for chart deletion, but it references a chart name, such as Chart58. Because each time the macro is run a new chart name would be assigned, I can't use this kind of name. I have tried deleting rows as well. That works when I do it, but not when I run the code. Any ideas!
Thanks!!!!

[XLGibbs]

If the code creates a chart, name the chart within the code. You can Delete the chart the same way. If the chart were simply named "MyChart"

It would be something like

ActiveSheet.ChartObjects("MyChart").Delete

The Chart58 incremental naming is the default.

To start, rename the 1 remaining chart you have to the new name (make sure the code you have assigns that name to the name property of the new chart...)

When you add the chart, specify the name

ActiveSheet.ChartObjects.Add ("MyChart")

Assigning a controlled name makes it easier.

[Aussiebear]

Can I just ask about the use of the phrases

ActiveSheet.ChartObjects("MyChart").Delete

and

ActiveSheet.ChartObjects.Add ("MyChart")

The name of the exixting chart is used before the ".delete" when deleteing yet when adding a chart the name of the object is used after the ".Add".


Does it always have to be in this order?

Ted

[Charlize]

I think it's because the chartobject isn't already present so you can't refer to it (when adding). When you delete, you have to specify the object to delete.

Charlize

[mdmackillop]

[VBA]Sub RenewChart()

Application.ScreenUpdating = False
On Error Resume Next
ActiveSheet.ChartObjects.Delete
On Error GoTo 0
Charts.Add
With ActiveChart
.ChartType = xlColumnClustered
.SetSourceData Source:=Sheets("Sheet1").Range("G1:H20")
.Location Where:=xlLocationAsObject, Name:="Sheet1"
End With
Application.ScreenUpdating = True

End Sub[/VBA]

[Aussiebear]

Thankyou.

[ActuarySpaz]

Hi again! Thanks for all the replies. I tried all of the suggestions, including the exact code from mdmackillop and it still won't delete the chart. Any clue on why this isn't working? Or if it's even possible? Thanks again!!!!!!

[mdmackillop]

Can you post a workbook sample? Use Manage Attachments in the Go Advanced section.

[ActuarySpaz]

Ok, I attached a portion of the code that I working with. Mine is much more detailed as far as the chart goes, but I guess you don't need that to see what I am doing wrong. Thanks so much for you help!

[mdmackillop]

Try stepping though this. Does it select anything?
[VBA] ActiveSheet.ChartObjects.Select
Selection.Delete
[/VBA]

[ActuarySpaz]

Ok, found out a couple of things:
1) It doesn't seem like it is selecting anything.
2) If I run the macro from the button on another sheet, it will run without errors, but will not actually delete the chart.
3) If I run the macro on the actual sheet holding the chart by selected Tools/Macro/Run Macro, it gives me this error "Object doesn't support this property or method." and highlights the "Selection.Delete" line.

I am so confused. I just can't figure out why this won't work! Again, any ideas are VERY appreciated. Thanks for all your help so far!

[ActuarySpaz]

I also found out if I use this:
ActiveSheet.ChartObjects.Select
ActiveSheet.ChartObjects.Delete

And I press the button, it doesnt work but runs. If I select this macro while on the sheet with the chart, it does.

[mdmackillop]

Can you post the actual workbook, removing any sensitive data.

[geekgirlau]

Quick question - why are you deleting the chart anyway? Doesn't it automatically refresh when your data changes?

[Dave]

Here's some example code for adding and then deleting a chart. HTH. Dave
[VBA]
Sub TempChart()
Dim ChartRange As Range
Dim Xvalue As Range, Yvalue As Range
'make chart (Sheet1 A1:B10 data)
Set Xvalue = Sheets("Sheet1").Cells(1, 1)
Set Yvalue = Sheets("Sheet1").Cells(10, 2)
Set ChartRange = Sheets("Sheet1").Range(Xvalue, Yvalue)
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=ChartRange, PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
MsgBox "The chart is about to delete"
'Delete chart
ActiveChart.ChartArea.Select
ActiveWindow.Visible = False
Selection.Delete
End Sub
[/VBA]

[ActuarySpaz]

Here is the workbook. A lot of the material is sensitive, so this won't make a lot of sense now. But the general idea is still there. Button is on the "Start" sheet.

As far as why I need to delete it, it's because the way I recorded the chart macro (because I couldn't easily write the code for the style of lines, ranges, etc) it generates a new chart each time.

Thanks again!

[ActuarySpaz]

Hey everyone! Got it to work! Just used the following:
On Error Resume Next
Sheets("Sheet8").Select
ActiveSheet.ChartObjects.Select
ActiveSheet.ChartObjects.Delete
On Error GoTo 0

I think the only thing I needed to do that I wasn't doing before was actually select the correct sheet. Thanks for your help!

[geekgirlau]

I'm glad it's working for you - make sure you mark the thread as "Solved" using the thread tools at the top of the window.