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, :hi:

If I understand correctly, this code will do itDim 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
*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



If CSng(srs.Points(pi).DataLabel.Text) _
> CSng(srs.Points(pi - modifier).DataLabel.Text) Then


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 :doh:

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 With srs
.HasDataLabels = True
.DataLabels.Type = xlValue
OldNumberFormat = .DataLabels.NumberFormat
.DataLabels.NumberFormat = "General"
End Withthen reapply the number format.End If
Next pi
srs.DataLabels.NumberFormat = OldNumberFormat
Next srsThat way it'll work on all charts. :thumb

[Shazam]

You are the man!!:super:



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 likeWith ActiveChart.SeriesCollection(2)
For intPoint = 1 To .Points.Count
.DataLabels(intPoint).Top = _
ActiveChart.SeriesCollection(1).DataLabels(intPoint).Top - 20
Next
End WithMake 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?