PivotChart Data Labels from Range in VBA

Question

I'm trying to edit a pivot chart so that its data labels are scaled down where necessary (i.e. 6 digit numbers are divided by 1,000), which requires the addition of custom labels from a cell range to the number to show that it's been scaled. For example, a number that was originally 800,000 will be converted to 800 within the PivotTable via a custom formula series, and then another cell with a formula in it will determine whether it's been scaled and show an "M" (for mile) if it has.

I can accomplish this statically without VBA simply by checking the "Value from Cells" box in addition to the "Value" box when formatting the Data Labels. However, changing the view, as I need to do constantly, resets the link I made when checking "Value from Cells." Additionally, my labels frequently revert back to being un-bolded and smaller than I made them. Therefore, I feel like I need to create a macro that will update my data labels to include a range lookup and resizing with every change to the table. My code to this end is below.

Dim numSubs As Integer
Dim Labels As Range
numSubs = 7 'find a way to count number of active series
For i = 1 To numSubs
    With Sheet16.ChartObjects("Chart 2").SeriesCollection(i).DataLabels
        .ShowRange = False
        .Format.TextFrame2.TextRange. _
            InsertChartField msoChartFieldRange, "='New PC Mapping'!$I$32:$I$34", 0 'find a way to cycle thru ranges
        .Format.TextFrame2.TextRange.Font.Bold = msoTrue
        .Format.TextFrame2.TextRange.Font.Size = 14
    End With
Next

First, VBA is telling me I can't run a SeriesCollection on my chart, which is the method shown in all the examples I've read. I have no idea what the sticking point is here. I've verified that it can identify Sheet16.ChartObjects("Chart 2")

Second, I need to find a way to cycle through multiple ranges, as each series has its own. My line of code here is from recording a macro because I couldn't find code for doing this anywhere online, so I'm sure it can be improved, but in the current form it's taking a string of the formula, which makes it difficult to move with a Range.Offset, for example.

From there, I know how to trigger it when the PivotTable is addressed. I just need some help getting it to work on just one series and I can generalize from there. Thanks!

When you say cycle through multiple ranges, what are the ranges you're talking about. How would you identify/differntiate them if just opening the sheet and looking..? — Chris Meurer
The answer to question 1 is that the SeriesCollection is a member of the [Chart or Chart group][1]. Changing Sheet16.ChartObjects("Chart 2").SeriesCollection(i).DataLabels to Sheet16.ChartObjects("Chart 2").Chart.SeriesCollection(i).DataLabels ought to give you the desired result. [1]: docs.microsoft.com/en-us/office/vba/api/excel.seriescollection — Chris Meurer
That worked, thanks! I've got a matrix in which each series should get mapped to a column of 3 values. I'm tinkering with this code: ~~~ Set dataLabelRange = Sheet24.Range("I32:I34").Offset(0, i) Set dataLabelFormula = "=" & CStr(dataLabelRange) And have dataLabelFormula replace the "='New PC Mapping'!... line, but still getting an error — Jakebd

Jakebd Jakebd · Accepted Answer · 2019-01-23T18:52:12

Thanks for the help in the comments, Chris!

I discovered a way to do the loop I need:

Set dataLabelRange = Sheet24.Range("I20:I22").Offset(0, i)
dataLabelString = "='New PC Mapping'!" & dataLabelRange.Address

With i++ each loop and dataLabelString inserted where the address goes above.

For anyone who needs a way to re-link addresses in a macro, e.g. triggered by a click on a pivot table that might sever these links, my final code is below:

Dim dataLabelRange As Range
Dim dataLabelString As String
Dim mySeries As Series
Dim i As Integer
i = 0
For Each mySeries In Sheet16.ChartObjects("Chart 1").Chart.FullSeriesCollection
    Set dataLabelRange = Sheet24.Range("I20:I22").Offset(0, i) 'runs through columns of 3 side by side, which is how I had the labels I wanted to add
    dataLabelString = "='*Sheet Name*'!" & dataLabelRange.Address
    mySeries.ApplyDataLabels
    With mySeries.DataLabels
        .ShowRange = False
        .Format.TextFrame2.TextRange.InsertChartField msoChartFieldRange, dataLabelString, 0
        .ShowRange = True
        .Format.TextFrame2.TextRange.Font.Bold = msoTrue 'addition code for formatting labels
        .Format.TextFrame2.TextRange.Font.Size = 14
        .Separator = " "
    End With
    i = i + 1
Next mySeries

PivotChart Data Labels from Range in VBA

1 Answers