excelvbaexcel-charts

Rename charts from "Graph number X"


I have code that generates graphs:

Sheets("kips").Select

Dim i As Integer 'rows
Dim j As Integer 'columns

i = Cells(Rows.Count, 1).End(xlUp).Row

For j = 2 To 5
    With ActiveSheet.Shapes.AddChart.Chart
        .ChartType = xlColumnClustered
        .SeriesCollection.NewSeries
        
        With .SeriesCollection(1)
            '.Name = "=" & ActiveSheet.Name & "!" & _
            'Cells(1, j).Address
            .XValues = "=" & ActiveSheet.Name & "!" & _
            Range(Cells(2, 1), Cells(i, 1)).Address
            .Values = "=" & ActiveSheet.Name & "!" & _
            Range(Cells(2, j), Cells(i, j)).Address
        End With
        
    End With
Next j

I need these four graphs created, to come out with a name, because it creates and uses something with "Graph number X" and the rest of the code doesn't work, as I give it a name as soon as I create it.

The rest of the code.

'Clean the charts
'chart1

ActiveSheet.ChartObjects("Gráfico 12").Activate
ActiveChart.ChartTitle.Select
Selection.Delete
ActiveSheet.ChartObjects("Gráfico 12").Activate
ActiveChart.Axes(xlValue).MajorGridlines.Select
Selection.Delete
ActiveSheet.ChartObjects("Gráfico 12").Activate
ActiveChart.Axes(xlValue).Select
Selection.Delete
ActiveSheet.ChartObjects("Gráfico 12").Activate
ActiveChart.Legend.Select
Selection.Delete

ActiveSheet.ChartObjects("Gráfico 12").Activate
ActiveChart.FullSeriesCollection(1).Select
ActiveChart.ChartGroups(1).Overlap = -27
ActiveChart.ChartGroups(1).GapWidth = 50
With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(0, 32, 96)
    .Transparency = 0
    .Solid
End With

Solution

  • For j = 2 To 5
         With ActiveSheet.Shapes.AddChart.Chart
             .Parent.Name = "Chart_" & (j-1)       '<< name the chartobject (Parent of Chart)
             '...
             '...    
    

    FYI it would probably be "cleaner" to pass the ChartObject directly to a formatting Sub, instead of name it and then later access it by name

    For j = 2 To 5
        With ActiveSheet.Shapes.AddChart.Chart
            FormatChart1 .Parent
            '...
            '...    
    

    Formatting sub:

    Sub FormatChart1(co as chartobject)
        With co
            .Chart.ChartTitle.Delete
            .Chart.Axes(xlValue).MajorGridlines.Delete
            .Chart.Axes(xlValue).Delete
            'etc
            'etc
        End with
    End sub