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!

user3598756 · Accepted Answer · 2016-10-26 16:03:05Z

3

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

edited Oct 26, 2016 at 16:03

answered Oct 26, 2016 at 13:43

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Gabriel Menezes Over a year ago

Thanks for the help! I am getting the Runtime Error 6: Overflow of the line Case CInt(.Text) = CDbl(.Text) As I am not familiar with these methods, I have to ask. This Range(dataSource).Cells(1, 1) Is it looking at the chart values or it's name for the formats? Because if it's looking at the formats, I don't think it's going to work, since the values are determined by lookup formula. If it's looking at the name, the format code is at one cell before (offset (0 , -1)).

user3598756 Over a year ago

The overflow must be due to CInt(.Text) when .Text exceeds the range –32,768 to 32,767 (I did my tests with little integers...). See edited answer where .Text is first cast to a Double (which ranges from –1.79769313486232E+308 to 1.79769313486232E308, should be big enough ...) and then rounded down to the nearest integer by means of Int() function

user3598756 Over a year ago

Range(dataSource).Cells(1, 1) references the first cell of the data source. In my test I assumed a "line chart" so that Sheets("Dashboard").ChartObjects(whichchart).Chart.SeriesCollection(i).Formula returned a string like =SERIES(,,sheetname!sheetRange,1) whose 3rd "element" (i.e. sheetname!sheetRange) is the "full" address of the values range

Gabriel Menezes Over a year ago

I used conditional formatting to apply the format directly to the data source, as well as changed the 'CInt(.Text)' to 'CLng(.Text)' and the problems were solved!! Thank you very much, user3598756!!

Collectives™ on Stack Overflow

Formatting chart data labels with VBA

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related