Shazam
11-02-2007, 08:53 AM
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
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