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]

OK, I have one sheet with embedded chart so I can test that. However, in one place you use ch and in another, chrt. Obviously you don't use Option Explicit in your code, otherwise it would not compile and you wouldn't get away with that. It may be your issue.

 

[Micron]

This may be the solution

Sub updateChart()
Dim ch As ChartObject
Dim i As Integer
Dim sh As Worksheet

Set sh = ActiveSheet
i = 1
For Each ch In sh.ChartObjects
    If InStr(1, ch.Chart.SeriesCollection(i).Name, "Branch") > 0 Then
        ch.Chart.SeriesCollection(i).Name = Replace(ch.Chart.SeriesCollection(i).Name, "Branch", "AVM")
    End If
Next

End Sub

ch.Chart.SeriesCollection

 

[BuJay]

This may be the solution

Sub updateChart()
Dim ch As ChartObject
Dim i As Integer
Dim sh As Worksheet

Set sh = ActiveSheet
i = 1
For Each ch In sh.ChartObjects
    If InStr(1, ch.Chart.SeriesCollection(i).Name, "AVM") > 0 Then
        ch.Chart.SeriesCollection(i).Name = Replace(ch.Chart.SeriesCollection(i).Name, "AVM", "Branch")
    End If
Next

End Sub

ch.Chart.SeriesCollection

Thanks Micron!

This seems to work for the first series....so, assuming a chart as two lines. The first line series is called AVM and the second series is called NonAVM. The code above changes AVM to Branch but does not change NonAVM to NonBranch.

I am looking for the code to change AVM to Branch and NonAVM to NonBranch. Then it needs to do this for all charts in the worksheet that contain Series 1 = Branch and Series 2 = NonBranch.

 

[Micron]

Then repeat the If ... End If for the other requirements?
I have to run out so can't look at it now.

 

[BuJay]

Then repeat the If ... End If for the other requirements?

I tried that - I’m sorry. I stepped away. I’ll try it again and share the attempt if it doesn’t work. Appreciate your help.

 

[Micron]

Then post what you tried?

 

[BuJay]

I think this worked.....strange because this is what I "thought" I did last time....marked your earlier code as the solution since that was essentially what I needed.

Thanks very much!

Sub updateChart2()

Dim ch As ChartObject
Dim i, j As Integer
Dim sh As Worksheet

Set sh = ActiveSheet

i = 1
j = 2

    For Each ch In sh.ChartObjects
        If InStr(1, ch.Chart.SeriesCollection(i).Name, "Branch") > 0 Then
            ch.Chart.SeriesCollection(i).Name = Replace(ch.Chart.SeriesCollection(i).Name, "Branch", "AVM")
        End If
       
        If InStr(1, ch.Chart.SeriesCollection(j).Name, "NonBranch") > 0 Then
            ch.Chart.SeriesCollection(2).Name = Replace(ch.Chart.SeriesCollection(2).Name, "NonBranch", "NonAVM")
        End If
   
    Next

End Sub

 

[Micron]

That could probably use some tweaking? What if you add another sheet with embedded charts? They won't be included in the code, right?
NB - i is a variant because it's not explicitly declared. If you're interested, I think there is a way to do this without a second variable and it would handle multiple series. Instead of multiple If blocks, I'm thinking a Select Case block. However, if you're happy with what you have, then don't worry about modifying.

marked your earlier code as the solution

Except you marked your own post as the solution.

 

[BuJay]

Jeez! Sorry about that. I think it’s corrected now….tough doing this on a cell phone.

 

[Micron]

Sometime I like to play so here's the result of that in case you're interested. It should loop over all series in all charts in all sheets. I say 'should' because I only have one sheet with an embedded chart but it does loop over 8 series. If Replace function doesn't find the word it does nothing, so testing if a variable > 0 for that doesn't seem necessary.

Dim ch As ChartObject, objSeries As Series
Dim sh As Worksheet

Set sh = ActiveSheet
For Each sh In ThisWorkbook.Worksheets
    If sh.ChartObjects.Count > 0 Then
        For Each ch In sh.ChartObjects
            For Each objSeries In ch.Chart.SeriesCollection
                objSeries.Name = Replace(objSeries.Name, "Branch", "AVM")
                objSeries.Name = Replace(objSeries.Name, "NonBranch", "NonAVM")
             Next
         Next
    End If
Next