Code goes into a infinite loop

[Shazam]

Hi everyone


I have this code below that will postion the vaules on the chart and it works grest. So I apply this code to a pivot chart and does not work properly. Everytime I use the drop down boxes it goes into a infinite loop.

Is there anyway to solve this?

Here is a link where I got the code from.
http://vbaexpress.com/forum/showthread.php?t=7497

 
Private Sub Chart_Calculate()
    Dim intSeries As Integer
    Dim intPoint As Integer
 
    Dim pi As Long
    Dim modifier As Integer
    Dim OldNumberFormat As String
 
       If Not ActiveChart Is Nothing Then
    Application.ScreenUpdating = False
    For Each srs In ActiveChart.SeriesCollection
         'make sure we have datalabels and they hold values
        With srs
            .HasDataLabels = True
            .DataLabels.Type = xlValue
            OldNumberFormat = .DataLabels.NumberFormat
            .DataLabels.NumberFormat = "General"
        End With
 
        For pi = 1 To srs.Points.Count
             'set up an exception for the first point
            If pi = 1 Then
                modifier = -1
            Else
                modifier = 1
            End If
             'decide where the label goes
            If CSng(srs.Points(pi).DataLabel.Text) _
            > CSng(srs.Points(pi - modifier).DataLabel.Text) Then
                srs.Points(pi).DataLabel.Position = xlLabelPositionAbove
            Else
                srs.Points(pi).DataLabel.Position = xlLabelPositionBelow
            End If
        Next pi
        srs.DataLabels.NumberFormat = OldNumberFormat
    Next srs
    Application.ScreenUpdating = True
 
End If
 
End Sub

[Bob Phillips]

Is it because the changes to the chart cause a re-entry of the calculate routine?

Try disabling application events at the start and turn them on at the end.

[Shazam]

Thank You xld I did what you told me to and it works great.

 
Private Sub Chart_Calculate()
 
    Dim pi As Long
    Dim modifier As Integer
    Dim OldNumberFormat As String
 
    Application.EnableEvents = False
 
       If Not ActiveChart Is Nothing Then
    Application.ScreenUpdating = False
    For Each srs In ActiveChart.SeriesCollection
         'make sure we have datalabels and they hold values
        With srs
            .HasDataLabels = True
            .DataLabels.Type = xlValue
            OldNumberFormat = .DataLabels.NumberFormat
            .DataLabels.NumberFormat = "General"
        End With
 
        For pi = 1 To srs.Points.Count
             'set up an exception for the first point
            If pi = 1 Then
                modifier = -1
            Else
                modifier = 1
            End If
             'decide where the label goes
            If CSng(srs.Points(pi).DataLabel.Text) _
            > CSng(srs.Points(pi - modifier).DataLabel.Text) Then
                srs.Points(pi).DataLabel.Position = xlLabelPositionAbove
            Else
                srs.Points(pi).DataLabel.Position = xlLabelPositionBelow
            End If
        Next pi
        srs.DataLabels.NumberFormat = OldNumberFormat
    Next srs
    Application.ScreenUpdating = True
 
End If
Application.EnableEvents = True
 
End Sub

Can you tell me how these lines work?

For pi = 1 To srs.Points.Count
             'set up an exception for the first point
            If pi = 1 Then
                modifier = -1
            Else
                modifier = 1
            End If
             'decide where the label goes
            If CSng(srs.Points(pi).DataLabel.Text) _
            > CSng(srs.Points(pi - modifier).DataLabel.Text)

[Bob Phillips]

It's a cute way to alternate the labels above and below the data point. Useful if there are many squeezed closely together.

[Shazam]

It's a cute way to alternate the labels above and below the data point. Useful if there are many squeezed closely together.

Thanks!