VBA to change all chart legend texts from a to b

[BuJay]

Assume I have a few hundred charts in an excel workbook. Let's assume there are two lines on each chart - 1 line series is named AAA and therefore, the legend for this series shows AAA. The other series is named NonAAA and so, the legend labels this series as NonAAA.

Is there a way to use VBA to go through every single chart and replace AAA in each series name with BBB - or alternatively, is there a way to change the text labels in all legends so that they say BBB and NonBBB for all charts?

Thanks!

 

[Micron]

There seems to be a solution here that looks like

Try changing these lines ...

Set Srs1 = ActiveChart.SeriesCollection(1)
Srs1.Name = "Current State"
Set Srs2 = ActiveChart.SeriesCollection(2)
Srs2.Name = "Proposed Solution"

To ...

.SeriesCollection(1).Name = "Current State"
.SeriesCollection(2).Name = "Proposed Solution"

How to change series name in VBA

I have a series of charts I am creating using VBA (code below). I am having trouble changing the names of the series from series 1 and series 2 to Current State and Solution State. I keep getting...

stackoverflow.com

 

[BuJay]

There seems to be a solution here that looks like

Try changing these lines ...

Set Srs1 = ActiveChart.SeriesCollection(1)
Srs1.Name = "Current State"
Set Srs2 = ActiveChart.SeriesCollection(2)
Srs2.Name = "Proposed Solution"

To ...

.SeriesCollection(1).Name = "Current State"
.SeriesCollection(2).Name = "Proposed Solution"

How to change series name in VBA

I have a series of charts I am creating using VBA (code below). I am having trouble changing the names of the series from series 1 and series 2 to Current State and Solution State. I keep getting...

stackoverflow.com

That isn't really what I am looking for...I'll try to provide additional info

 

[BuJay]

I have this code that works to update the actual series references. However, this is not what I need.

Sub updateChart()

Dim sh As Worksheet
Set sh = ActiveSheet

'For each chart on the selected sheet
For Each ch In sh.ChartObjects
For Each srs In ch.Chart.SeriesCollection
'i = InStr(srs.Formula, "HFI_") + InStr(srs.Formula, "Portfolio")
i = InStr(srs.Formula, "HLSC_")
Debug.Print i

'if i is greater than 0 it means that the series has the old word
If i > 0 Then
'replace the old word with the new word
'newSrs = Replace(Replace(srs.Formula, "portfolio", "investor"), "Portfolio", "Investor")
newSrs = Replace(srs.Formula, "HLSC_", "Branch_")
Debug.Print newSrs



'update the series
srs.Formula = newSrs
End If
'next series
Next
'next chart
Next

End Sub



What I need is to change the "Names" of the series....I think the object is ActiveChart.FullSeriesCollection(1).Name

Is there a way to create a loop that says

For each chart in the worksheet,
If the ActiveChart.FullSeriesCollection(1).Name = "AAA", then change to "NotAAA"
If the ActiveChart.FullSeriesCollection(2).Name = "BBB", then change to "NotBBB"
end

 

[Micron]

I have little experience with chart vba but think it should be possible and would be willing to dabble (which usually results in me writing code only to have someone else who's more experience beat me to the punch).
OK, while holding this post open, I wrote this, which works for my test. I think it can be simplified though but wanted to get it in here first.

Sub test()
Dim i As Integer
Dim chrt As Chart

i = 1
If ThisWorkbook.Charts.Count > 0 Then
    For Each chrt In ThisWorkbook.Charts
        If InStr(1, chrt.SeriesCollection(i).Name, "Onsite") > 0 Then
            chrt.SeriesCollection(i).Name = Replace(chrt.SeriesCollection(i).Name, "Onsite", "Offsite")
        End If
    Next
End If
   
End Sub

Please use code tags (vba button on posting toolbar) to maintain indentation and readability of code.

 

[Micron]

Since you know the series names, this also works. If you have a lot of series there might be a better way than writing a line for each one. Also, if this is going to happen frequently, then you may want to pick up the change values from inputs (cells, input box, whatever).

If ThisWorkbook.Charts.Count > 0 Then
    For Each chrt In ThisWorkbook.Charts
        chrt.SeriesCollection(1).Name = Replace(chrt.SeriesCollection(1).Name, "Offsite", "Onsite")
        chrt.SeriesCollection(2).Name = Replace(chrt.SeriesCollection(2).Name, "Financial", "Financials")
        chrt.SeriesCollection(3).Name = Replace(chrt.SeriesCollection(3).Name, "Average", "Averages")
    Next
End If

 

[BuJay]

Tried this but getting an error

Sub updateChart()

Dim i As Integer
Dim sh As Worksheet
Set sh = ActiveSheet

i = 1

'For each chart on the selected sheet
    For Each ch In sh.ChartObjects
        
        If InStr(1, chrt.SeriesCollection(i).Name, "Branch") > 0 Then
            chrt.SeriesCollection(i).Name = Replace(chrt.SeriesCollection(i).Name, "Branch", "AVM")
        End If
    
    Next


End Sub

 

[Micron]

You really ought to say what the error is.
I'll guess - it's because a chart is not a worksheet - it is a sheet though. However I don't think Sheet will work, which is why I declared it as a chart.
EDIT - I think I'm mistaken about being a worksheet.

 

[Micron]

Update - it could be that your sheet is a sheet of type "Chart", not a worksheet with a chart on it. The latter looks like this

a chart sheet has no gridlines, rows or columns

 

[BuJay]

Sorry @Micron - the error said something like "object not defined". The charts are not separate chart sheets - they are simply created in a sheet call "charts".