Solved: Position show values on chart

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.

Code:

---


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

---

Hi there, :hi:

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

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

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 [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. :thumb

You are the man!!:super:



Thank You!

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:

Code:

---


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

---

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

Once again Thank You for your magic.

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?