Formatting chart data labels with VBA

Question

I am developing a dashboard that will have lots of charts, and as the data shown on these charts change, so will the format of the numbers. At the point I am right now, I've run into a problem trying to retrieve the intended format code from the spreadsheet where the data is based on, in the midst of looping through all the series in the chart. Here's the code so far:

Sub FixLabels(whichchart As String)
Dim cht As Chart
Dim i, z As Variant
Dim seriesname, seriesfmt As String
Dim seriesrng As Range

Set cht = Sheets("Dashboard").ChartObjects(whichchart).Chart

    For i = 1 To cht.SeriesCollection.Count
        If cht.SeriesCollection(i).name = "#N/D" Then
            cht.SeriesCollection(i).DataLabels.ShowValue = False
            Else
            cht.SeriesCollection(i).DataLabels.ShowValue = True
                seriesname = cht.SeriesCollection(i).name
                Debug.Print seriesname

With this I am able to retrieve the name of de series that do not result in error, and hide the series that do. So far so good. Now on to the formatting: There's a column where all the possible series names for this workbook are stored, and one column to the left, there are my formatting codes, which are "int", for integer numbers, "#,#" for numbers with important decimal cases, and "%" for percent rates. These are stored as plain text. So the last bit of the code would look like:

Select Case seriesfmt
   Case "int"
      Cht.SeriesCollection(i).DataLabels.NumberFormat = "#"
   Case "#,#"
      Cht.SeriesCollection(i).DataLabels.NumberFormat = "#,###"
   Case "%"
      Cht.SeriesCollection(i).DataLabels.NumberFormat = "#.0%"
End Select
End If
Next i

Finally the real problem here: what goes in between. I cannot retrieve the series format! My best guess was:

With Sheets("CxC").Range("K22:K180")
seriesfmt = .Find(seriesname).Offset(0, -1).Value
End With

I got errors, telling me the With block was not defined. I tried several combinations of the same command, with or without the With method, with and without the Set method, I tried WorksheetFunction Match, to no avail. Any help solving this issue is greatly apreciated!

Answer 1

You can climb up to a series source range via its Formula property.

Since it has the format:

=SERIES(,,sheetname!sheetRange,)

then you're interested in its "3rd element", if you split it into an array with "," as delimiter

so you can code:

Sub FixLabels(whichchart As String)
    Dim cht As Chart
    Dim i As Long

    With Sheets("Dashboard").ChartObjects(whichchart).Chart '<--| reference your chart
        For i = 1 To .SeriesCollection.Count '<--| loop through all series
            With .SeriesCollection(i) '<--| reference current series
                If .Name = "#N/D" Then
                    .DataLabels.ShowValue = False
                Else
                    .HasDataLabels = True '<--| be sure labels are "activated"
                    .DataLabels.ShowValue = True '<--| show data labels
                    .DataLabels.NumberFormat = GetFormat(Split(.Formula, ",")(2)) '<-- set data label format
                End If
            End With
        Next i
    End With
End Sub


Function GetFormat(dataSource As Variant) As String
    With Range(dataSource).Cells(1, 1) '<-- reference the first cell of the data source
        Select Case True
            Case InStr(.Text, "%") > 0
                GetFormat = "#.0%"
            Case Int(CDbl(.Text)) = CDbl(.Text)
                GetFormat = "#"
            Case Else
                GetFormat = "#,###"
        End Select
    End With
End Function