Rename a data series in a Box and Whisker chart - Macro or VBA

[PaulClarke]

I need to automate the operation described in the Microsoft Support page Rename a data series
I need to do this only on Box and Whisker charts.
For example:

To get from the chart above to the chart below is easily performed manually

Recording a macro of the operation of that example gives:

*****

Sub Macro3()


' Macro3 Macro


ActiveChart.FullSeriesCollection(2).Select

Application.CutCopyMode = False

ActiveChart.FullSeriesCollection(1).Name = "=""1"""

ActiveChart.FullSeriesCollection(2).Name = "=""2"""

ActiveChart.FullSeriesCollection(3).Name = "=""3"""

End Sub

*****

When played back, the macro does not complete (it hangs part way through).

This example includes only 3 values. My projects involve dozens of values to rename in the same B&W chart - in order to reduce the size of the legend (deleting the repetition of the word ‘Series’) – and many, many charts.

If anyone can help with a macro that works, I can then add lines of code to handle the repetitions.

Here's hoping !

With thanks,
Paul

 

[severynm]

Hi and welcome!

Especially if you are potentially working with multiple charts, but even if not, I would definitely recommend against using ActiveChart or any other similar Active or Selected object - you can easily run into trouble when everything is a "relative" reference. It is much better practice overall, and it makes it easier to debug and read code when you refer to a chart specifically (see below for an example).

Anyways, to rename a chart series, the best way to do that is like this answer. If you're renaming dozens of series on a chart, it may be helpful to loop through them all

Sub RenameChartSeries_1179067()
    Dim boxchart As chart
    Dim idx As Integer
    Set boxchart = ThisWorkbook.Worksheets("1179067").ChartObjects(1).chart
    
    For idx = 1 To boxchart.SeriesCollection.Count
        boxchart.SeriesCollection(idx).Name = "renamed " & idx
    Next idx
End Sub

 

[PaulClarke]

Thank you severynm. In fact, I managed to solve my problem before you replied yesterday - using the coding you suggested - and in a loop .