Loop through chart and altering labels (Category Name)

I have a data set, exported from a SharePoint into Excel that we generate various charts from.

I need to simplify the "category names" because, in some cases, they are super long and make charts look terrible. These category names are known by folks in my office by much shorter acronyms, so changing them to these acronyms would be OK. I have probably 15 category names + replacement acronyms.

I'd like to loop through all the charts in a workbook and, for example, do something like this:

  • "AAAA, 45%", change it to "AA, 45%"
  • "BBBB, 22%", change it to "BB, 22%"
  • "CCCC, 67%", change it to "CC, 67%"

Some basic Google fu has produced a basic loop (below, but not working), but I'm not familiar enough with chart and label objects to take the next step and make edits to the category labels. Obviously, I only want to alter the category, not the calculated value - the percentage in the above examples. Can anyone assist?

With ActiveChart     
    For k = 1 To .SeriesCollection.Count         
        For j = 1 To .SeriesCollection(k).Points.Count             
            If .SeriesCollection(k).Points(j).DataLabel.Caption = "AAAA" Then 
                .SeriesCollection(k).Points(j).DataLabel.Caption = "AA"            
            End If 
        Next j     
    Next k 
End With
728x90

1 Answers Loop through chart and altering labels (Category Name)

One idea might be the Replace function.

This can probably be made more robust, but should get you started:

Sub ShortenLabels()
    Dim k As Long, j As Long

    With ActiveChart
        For k = 1 To .SeriesCollection.Count
            For j = 1 To .SeriesCollection(k).Points.Count
                With .SeriesCollection(k).Points(j).DataLabel
                    .Caption = Replace(.Caption, "AAAA", "AA")
                    .Caption = Replace(.Caption, "BBBB", "BB")
                    .Caption = Replace(.Caption, "CCCC", "CC")
                End With
            Next j
        Next k
    End With
End Sub

4 months ago