Aligning data labels in a chart

[TrippyTom]

I pretty much have my chart setup the way I want (I think), but I always have to manually position the percentages. Is there a better way to set this up so I don't have to do that?

[Bob Phillips]

Do it in Excel with the macro recorder on, and you will have the code.

[TrippyTom]

oh wow, that was rather easy. I guess I didn't think of doing it through code. I was thinking there might be a way to do it through the chart setup.

Now I just have to cycle through all the data labels, get their top and left, assign it to the % labels and adjust the left position to the right a little bit.

Thanks ...

[Bob Phillips]

I have written code like that before, and I beleieve you just grab the series and align the the whole set of labeles, like so (off the top}


Dim i As Long
With ActiveSheet.ChartObjects("Chart 1").Chart
For i = 1 To .SeriesCollection.Count
With .SeriesCollection(i).DataLabels
.VerticalAlignment = xlCenter
End With
Next i
End With

[TrippyTom]

oh but i want the % label to line up with it's corresponding series label. For instance 63% would line up with 25.00 in the chart above. I think I have to get the specific top/left of that label to line it up, wouldn't I? (instead of using xlcenter)

[Bob Phillips]

How were the percentages setup to the chart?

[TrippyTom]

they were just data labels of separate series on 2ndary vertical axis.

Here's the code I came up with. Seems to work but if you wanna tweak it, be my guest. :)


Sub AlignPercentages()
With ActiveSheet.ChartObjects("Chart 1").Chart
For j = 1 To 6 Step 2
For i = 1 To 3
.SeriesCollection(j).Points(i).DataLabel.Select
mytop = Selection.Top
myleft = Selection.Left + 35
.SeriesCollection(j + 1).Points(i).DataLabel.Select
Selection.Top = mytop
Selection.Left = myleft
Next i
Next j
End With
End Sub

[TrippyTom]

and here's my file in Excel 2010 that I was using as a tester.

[Bob Phillips]

If it works, why would I mess with it :)