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