PDA

View Full Version : [SOLVED] Aligning data labels in a chart



TrippyTom
07-18-2010, 08:31 AM
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
07-18-2010, 08:47 AM
Do it in Excel with the macro recorder on, and you will have the code.

TrippyTom
07-18-2010, 09:13 AM
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
07-18-2010, 09:45 AM
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
07-18-2010, 10:14 AM
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
07-18-2010, 10:54 AM
How were the percentages setup to the chart?

TrippyTom
07-18-2010, 11:07 AM
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
07-18-2010, 11:09 AM
and here's my file in Excel 2010 that I was using as a tester.

Bob Phillips
07-18-2010, 11:45 AM
If it works, why would I mess with it :)