rename charts in a boolean vba

Question

how are you? Can someone help me?

I have the following code that generates graphics:

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

it works well, but I need these 4 graphics created, 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 he?

here's 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

Tim Williams · Accepted Answer · 2021-02-19T21:11:56.217

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

rename charts in a boolean vba

1 Answers1