Solved: Position show values on chart

[Shazam]

Hi everyone,

I have 2 line charts and I update it on a weekly bases. I choose to show the values on the line charts. But when I update the chart the values gets shifted. If possible when I enter the
values in the cells it will position the values just above the data point. But here is the kicker if the values is less than the previous data point then it will position just under the data point. Is that possible?

I have attach the sample file below.

 
Private Sub Chart_Calculate() 
    Dim intSeries As Integer 
    Dim intPoint As Integer    
    With ActiveChart 
       For intSeries = 1 To .SeriesCollection.Count 
           With .SeriesCollection(intSeries) 
               Select Case intSeries ' may need to extend cases 
               Case 1 
                   .Interior.ColorIndex = 41 
               End Select 
               .ApplyDataLabels 
               .DataLabels.Position = xlLabelPositionInsideEnd 
               .DataLabels.Font.Bold = True 
               For intPoint = 1 To .Points.Count 
                   End Select 
                   .DataLabels(intPoint).Top = .DataLabels(intPoint).Top - 20 
               Next 
           End With 
       Next 
    End With 
End Sub

[Killian]

Hi there,

If I understand correctly, this code will do it[VBA]Dim srs As Series
Dim pi As Long
Dim modifier As Integer

For Each srs In ActiveChart.SeriesCollection
'make sure we have datalabels and they hold values
With srs
.HasDataLabels = True
.DataLabels.Type = xlValue
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
Next[/VBA]
*I also notice your chart had an extra blank series in it that made the code trip up

[Shazam]

Thank You so much for responding,



I ran you code on the first chart its fine but when I ran on the second chart its giving me a debugger


[vba]
If CSng(srs.Points(pi).DataLabel.Text) _
> CSng(srs.Points(pi - modifier).DataLabel.Text) Then
[/vba]

I attach the file below with your code

[Killian]

Well this is because code compares the values of two datapoints' label text and my sloppy code was trying to cast the string "4.42%" to a single when the charts were in percent

So to correct this horrible oversight, when we start the loop for each series, we can save its datalabel number format and change it to general so we can do our comparison with the raw number [VBA]With srs
.HasDataLabels = True
.DataLabels.Type = xlValue
OldNumberFormat = .DataLabels.NumberFormat
.DataLabels.NumberFormat = "General"
End With[/VBA]then reapply the number format.[VBA]End If
Next pi
srs.DataLabels.NumberFormat = OldNumberFormat
Next srs[/VBA]That way it'll work on all charts.

[Shazam]

You are the man!!



Thank You!

[Shazam]

Hi me again,


Same problem but I can't get the second series position vaules the way I would like. I attach the sample file below. The worksheet tab named "Result" is what I would like. Can it modified?

Maybe we could use something like this:

 
.DataLabels(intPoint).Top = .DataLabels(intPoint).Top - 20

[Killian]

Well you need to position each label for series 2 relative to the label for series 1, so something like[VBA]With ActiveChart.SeriesCollection(2)
For intPoint = 1 To .Points.Count
.DataLabels(intPoint).Top = _
ActiveChart.SeriesCollection(1).DataLabels(intPoint).Top - 20
Next
End With[/VBA]Make sure you have enough space for both labels to fit or they'll overlap

[Shazam]

Once again Thank You for your magic.

[Shazam]

Hi Killian,


I have these charts that the show vaules are crunch up together. Is it possible to have these values space from one another. Also if any of the show vaules show a zero percentage on the pie chart can that zero percentage be deleted in the pie charts?