Excel Crashes When VBA Updates Chart Labels After Pivot Chart Filter Changed

[dendres]

Hi,

I am using Excel 2010 on Windows 7.

I have some pivot charts that have custom labels. When the workbook is first opened, I run the Sub called AddDataLabelAsValues. This is called a few times and runs through a few ChartSheets and assigns custom labeling to the columns. For each pivot chart, the custom labels come from a secondary pivot table that is directly linked to the primary one by use of slicers. The user never sees the pivot tables or slicers. But having two is necessary and I will leave out the rest of the details as I don't believe in any way they are relevant.

The problem arises when the user changes the filter settings on the pivot chart by using one of the field buttons. When they do that, I need to call the AddDataLabelAsValues routine to relabel my chart since the related pivot table changes.

Below is the sub routine:

Sub AddDataLabelAsValues(strChartName As String)
    
    Dim srs As Series
    Dim rng As Range
    Dim lbl As DataLabel
    Dim iLbl As Long
    Dim nLbls As Long
    Dim sFmla As String
    Dim sTemp As String
    Dim vFmla As Variant
    
    GoToChart strChartName
    
    If Not ActiveChart Is Nothing Then

        Set srs = ActiveChart.SeriesCollection(1)

        If Not srs Is Nothing Then
            
            ' parse series formula to get range containing Y values
            sFmla = srs.Formula
            sTemp = Mid$(Left$(sFmla, Len(sFmla) - 1), InStr(sFmla, "(") + 1)
            vFmla = Split(sTemp, ",")
            sTemp = vFmla(LBound(vFmla) + 2)
            On Error Resume Next
            Set rng = Range(sTemp)
    
            If Not rng Is Nothing Then
                ' use offset to find column with data labels that are desired
                Set rng = rng.Offset(, 7)

                ' point by point, assign cell's address to label
                nLbls = srs.Points.Count
                
                If rng.Cells.Count < nLbls Then
                    nLbls = rng.Cells.Count
                End If
                
                For iLbl = 1 To nLbls
                    srs.Points(iLbl).HasDataLabel = True
                    Set lbl = srs.Points(iLbl).DataLabel
                    With lbl
                      .Text = "=" & rng.Cells(iLbl).Address(External:=True)
                      .Position = xlLabelPositionRight
                    End With
                Next
            
            End If
            
        End If
    
    End If

End Sub

I've tried to call this from several places in VBA, but every time I do, Excel crashes. Here are some of the ones I've tried:

• Chart_Calculate
• Worksheet_PivotTableChangeSync
• Worksheet_PivotTableUpdate

I've also tried using Application.EnableEvents = True/False in more places than I care to remember. Although I do believe this is related to what's going on.

I've run all Windows updates and run the repair tool for Office 2010.

Any help would truly be appreciated. Hopefully I am missing something trivial and easy to fix...

Thanks
dendres

 

[dendres]

Hello,

Sorry to reply to my own thread, but it's been nearly a month and perhaps a fresh set of eyes will see my question and have some insight into it?

Thank you in advance.
dendres