There may be times when you want to present a chart with labels aligned evenly on a horizontal plane. As an example, begin with the chart below:

The procedure below will loop through each point in the series and evenly position the data labels associated with those points.

Sub AlignDataLabels()
  Dim Cnt As Long
  Set Srs = ActiveChart.SeriesCollection(1)
  With Srs
    For Cnt = 1 To .Points.Count
        Srs.Points(Cnt).DataLabel.Top = 135
    Next
  End With
End Sub

The end-result looks like the chart below. All of the data labels are evenly positioned near the base of the bars.

Why not just use the Inside Base alignment option of the data labels? This eliminates the need for code in your workbook.

Hello Jon.  Nice to hear from you.  After almost two years of working away from home on an accounting systems implementation that required literally all of my time, that project is finally done and I thought I would get back into studying Excel.

I posted this VBA example because it was something that I recently needed to create at work.  Although the post shows a single set of labels at the inside base of the graph, my need for work was to be able to align the labels anywhere on the chart.  When the chart for work was finally complete, the end result was to align all of the labels against the top edge of the chart.  You’re correct though - for the example I’ve shown the Inside Base option would have done the same thing.

Hi John -

It was good to see you start posting again. You had disappeared, your blog went belly up, and emails went unanswered. Welcome back.

What I do when I need custom labels in non-standard positions is add XY series to the chart, tailoring the X and Y values of the points to precisely locate the labels, and apply custom labels to the points. I can use formulas in the worksheet to change X and Y and even change the custom text of the labels. Once the chart is created, there’s no need for VBA to update it if all that changed was the data.

There are many ways to skin a cat in Excel, and I tend to look for ways that don’t require VBA after a workbook has left my possession.