VBA Code to move DataLables to top of Chart

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Frank Hayes - 13 Oct 2007 12:20 GMT

I am creating a series of column charts, and I would like to move all the
data labels in a series to the top of the chart (above the column and evenly
spaced)

The VBA code to do so for any individual point in the series is:

' ActiveChart.SeriesCollection(3).Points(1).DataLabel.Select
' Selection.Top = 52

I would like to automate this to do it for every point in the
seriescollection, and have tried the following:

Sub LabelsToTop()

For Each Points In ActiveChart.SeriesCollection(4)
DatalLabel.Select
Selection.Top = 52
Next

End Sub

But get an Object Variable Error Messge. Does anyone know the correct
sytax ? I am using Excel 2000 with SP-3

Thanks

Frank

Reply to this Message

John Mansfield - 13 Oct 2007 13:23 GMT

Consider revising your For-Next statement to the following (also note that
this procedure should perform more efficiently as it does not use a "Select"
statement):

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

Signature

John Mansfield
http://cellmatrix.net

> I am creating a series of column charts, and I would like to move all the
> data labels in a series to the top of the chart (above the column and evenly
[quoted text clipped - 23 lines]
>
> Frank

Reply to this Message

Frank Hayes - 13 Oct 2007 14:14 GMT

Perfect. Thank you.

> Consider revising your For-Next statement to the following (also note that
> this procedure should perform more efficiently as it does not use a
[quoted text clipped - 40 lines]
>>
>> Frank

Reply to this Message

Jim Cone - 13 Oct 2007 13:23 GMT

Frank,
Try this...

Sub MoveLabelsUp()
Dim objPt As Point
For Each objPt In ActiveChart.SeriesCollection(3).Points
objPt.DataLabel.Top = 52
Next
End Sub

Signature

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Chart Data Labels Excel Add-in)

"Frank Hayes"
wrote in message
I am creating a series of column charts, and I would like to move all the
data labels in a series to the top of the chart (above the column and evenly
spaced)

The VBA code to do so for any individual point in the series is:

' ActiveChart.SeriesCollection(3).Points(1).DataLabel.Select
' Selection.Top = 52

I would like to automate this to do it for every point in the
seriescollection, and have tried the following:

Sub LabelsToTop()

For Each Points In ActiveChart.SeriesCollection(4)
DatalLabel.Select
Selection.Top = 52
Next

End Sub

But get an Object Variable Error Messge. Does anyone know the correct
sytax ? I am using Excel 2000 with SP-3
Thanks
Frank

Reply to this Message

Frank Hayes - 13 Oct 2007 14:15 GMT

Thank you.

> Frank,
> Try this...
[quoted text clipped - 5 lines]
> Next
> End Sub

Reply to this Message