Detect whether a chart exists in a sheet

Topic

New Reply

I am working with someone else here on an automation problem. he has developed an Excel spreadsheet that automatically creates sets of charts on various sheets. he would like to be able to detect whether there exists any charts on any given sheet so he can delete these charts programmatically. I have been looking into it, but thus far a method eludes me. What I would like to be able to do is,

Find and count any existing charts on a worksheet –> that way, a dynamic delete can be built.

TIA!

Reply | Quote

Replies

You could use ActiveSheet.Chartobjects.count

Reply | Quote

thanks – that should probably do it.

Reply | Quote

Hi Steve,

The following VBA should get you started – it includes a loop to examine all worksheets:

[indent]Sub chart_killer()

Dim n_charts As Long, k_chart As Long
Dim usr_msg As VbMsgBoxResult
Dim wk_sheet As Worksheet

For Each wk_sheet In ThisWorkbook.Sheets
n_charts = wk_sheet.ChartObjects.Count
If n_charts > 0 Then
wk_sheet.Activate
For k_chart = n_charts To 1 Step -1
wk_sheet.ChartObjects(k_chart).Activate
usr_msg = MsgBox(“Delete the selected chart?”, vbQuestion + vbYesNo, _
“Delete”)
If usr_msg = vbYes Then wk_sheet.ChartObjects(k_chart).Delete
Next k_chart
End If
Next wk_sheet

End Sub[/indent]

Note: Activate rather than Select
Hope this helps,

RAK.

Reply | Quote

we went with this one-liner:

ActiveSheet.ChartObjects.Delete

as there’s no need to know how many charts are to be deleted, just delete.

Reply | Quote