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 co…

Accepted Answer

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
```

Formatting chart data labels with VBA

Solution

Problem