VBA to change all chart legend texts from a to b

BuJay

Board Regular
Joined
Jun 24, 2020
Messages
75
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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!
 
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.
 
Upvote 0

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
This may be the solution
VBA Code:
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
 
Upvote 0
Solution
This may be the solution
VBA Code:
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.

1682704280484.png
 
Upvote 0
Then repeat the If ... End If for the other requirements?
I have to run out so can't look at it now.
 
Upvote 0
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.
 
Upvote 0
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!

VBA Code:
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
 
Upvote 0
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. ;)
 
Upvote 0
Jeez! Sorry about that. I think it’s corrected now….tough doing this on a cell phone. :)
 
Upvote 0
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.
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top