Chart Object As It's Own Sheet - How do you delete via VBA?

[jswhip]

I have a workbook with one data sheet and one chart as another sheet. I'm trying to write a macro that would delete the chart - however, Excel doesn't recognize chart as a sheet. The macro recorder lists the chart as a sheet.

How do I go about deleting the chart via VBA (I'm Excel 2007)?

 

[pedie]

try

[FONT=Courier New]Sub AllChartsDelete()[/FONT]
[FONT=Courier New]On Error GoTo ExitChart[/FONT]
[FONT=Courier New]Dim ws As Worksheet[/FONT]
[FONT=Courier New]Dim chObj As ChartObject[/FONT]
[FONT=Courier New]Application.DisplayAlerts = False[/FONT]
[FONT=Courier New]'delete embedded charts[/FONT]
[FONT=Courier New]For Each ws In ThisWorkbook.Worksheets[/FONT]
[FONT=Courier New]For Each chObj In ws.ChartObjects[/FONT]
[FONT=Courier New]chObj.Delete[/FONT]
[FONT=Courier New]Next chObj[/FONT]
[FONT=Courier New]Next ws[/FONT]
[FONT=Courier New]'delete the chart sheets[/FONT]
[FONT=Courier New]ThisWorkbook.Charts.Delete[/FONT]
[FONT=Courier New]ExitChart:[/FONT]
[FONT=Courier New]Application.DisplayAlerts = True[/FONT]
[FONT=Courier New]Exit Sub[/FONT]
[FONT=Courier New]End Sub[/FONT]

 

[jswhip]

Thanks for the reply.

That's the problem that I've ran into - I've used similar logic to defeat worksheets, but VBA doesn't recognize the chart as a worksheet, so it doesn't process it.

I've used this logic:

Sub SeeSheetNames()

Dim iSheetCount As Integer
Dim iSheet As Integer

iSheetCount = ActiveWorkbook.Sheets.Count
For iSheet = 1 To iSheetCount
Worksheets(iSheet).Activate
MsgBox Worksheets(iSheet).Name
Next iSheet

Using this method, the number of sheets is correct (2), and the name of the first worksheet shows up in the msgbox, but on the 2nd loop, I get a subscript out of range error -which indicates to me that Excel doesn't recognize the chart as a worksheet.

Any further thoughts?

 

[Richard Schollar]

Try:

For Each cht In ActiveWorkbook.Charts
  cht.Delete
Next cht

 

[jswhip]

Thank you.

What would you define "cht" as? I tried ChartObject, but then I get a type mismatch.

 

[jswhip]

Nevermind - I need to define "cht" as Chart.

Thanks for your help!