CODEWITHSUNDEEP
excelvbaexcel-charts
mountainclimber11
mountainclimber11

Reputation: 1400

Get chart title from ChartObjects

I want to be able to identify the chart by it's title (or other non-default name that I give it). Here I am trying to delete it if it has a certain title:

Sub delchart()

Call create_chart
Dim d

For Each d In Worksheets("sheet1").ChartObjects

    Debug.Print d.Name '<-not a usable name

    If d.ChartTitle = "Scatter Chart" Then '<- errors
        d.Delete
    End If
Next d
End Sub

Here is how I am creating the chart:

Sub create_chart()


    Dim sh As Worksheet
    Dim chrt As Chart

    Set sh = ActiveWorkbook.Worksheets("Sheet1")
    Set chrt = sh.Shapes.AddChart.Chart
    With chrt
        'Data?
        .ChartType = xlXYScatter
        .SeriesCollection.NewSeries
        .SeriesCollection(1).Name = "=""Scatter Chart"""
        .SeriesCollection(1).XValues = "=Sheet1!$A$2:$A$11"
        .SeriesCollection(1).Values = "=Sheet1!$B$2:$B$11"

        'Titles
        .HasTitle = True
        .ChartTitle.Characters.Text = "Scatter Chart"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X values"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Y values"
        .Axes(xlCategory).HasMajorGridlines = True

        'Formatting
        .Axes(xlCategory).HasMinorGridlines = False
        .Axes(xlValue).HasMajorGridlines = True
        .Axes(xlValue).HasMinorGridlines = False
        .HasLegend = False
        .Location Where:=xlLocationAsObject, Name:="Sheet1"
        '.Name = "my scatter 1"
    End With

End Sub

How do I delete the chart if it has a certain identify that I give it? Chart title is the most intuitive to me, but can't seem to be able to access the title via the ChartObject.

Upvotes: 0

Views: 817

Answers (1)

drec4s
drec4s

Reputation: 8077

You can access the ChartTitle text like this:

Sub delchart()

Call create_chart
Dim d

For Each d In Worksheets("sheet1").ChartObjects

    Debug.Print d.Name '<-not a usable name

    If d.Chart.ChartTitle.Caption = "Scatter Chart" Then
        d.Delete
    End If
Next d

End Sub

Upvotes: 1

Related Questions