CODEWITHSUNDEEP
excelvba
DeCodened
DeCodened

Reputation: 71

Check if a worksheet has charts and delete those existing

I am trying to implement a project that creates a new chart based on constantly updating information. I simply need to check if a chart pre-exists on a sheet (users could accidently delete previous versions). There seems to be a catch 22 that if a chart doesn't already exist, you can't seem to get the count of the Worksheet.ChartObjects property. I get an Application/Object defined error with the following code:

If Worksheets(1).ChartObjects.Count <> 0 Then
        Worksheets(1).ChartObjects(1).Activate
        For Each s In ActiveChart.SeriesCollection
            s.Delete
        Next
        Worksheets(1).ChartObjects(1).Delete
End If

It would seem that the count would be 0 if none exist, so how does one test for this? I looked at the Worksheets.Chart object, but that seems to call separate sheets that have only charts in them. I am looking to place a chart on an active sheet. Note I also need to delete each series as I was getting inconsistent results before. Thanks for your help in advance.

Upvotes: 1

Views: 8655

Answers (1)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96781

Consider:

Sub charttest()
    If ActiveSheet.ChartObjects.Count > 0 Then
        MsgBox "charts on this sheet"
    Else
        MsgBox "no charts on this sheet"
    End If
End Sub

This works for me.

NOTE:

The line:

 Worksheets(1).ChartObjects(1).Activate

will fail if Worksheets(1) is not active itself.

Upvotes: 2

Related Questions