VBA Code to move DataLables to top of Chart
 

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).DataLabe l.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

VBA Code to move DataLables to top of Chart
 

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

--
John Mansfield
http://cellmatrix.net


"Frank Hayes" wrote:

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).DataLabe l.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

VBA Code to move DataLables to top of Chart
 

Frank,
Try this...

Sub MoveLabelsUp()
Dim objPt As Point
For Each objPt In ActiveChart.SeriesCollection(3).Points
objPt.DataLabel.Top = 52
Next
End Sub
--
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).DataLabe l.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

VBA Code to move DataLables to top of Chart
 

Perfect. Thank you.


"John Mansfield" wrote in message
...
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

--
John Mansfield
http://cellmatrix.net


"Frank Hayes" wrote:

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).DataLabe l.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

VBA Code to move DataLables to top of Chart
 

Thank you.

"Jim Cone" wrote in message
...
Frank,
Try this...

Sub MoveLabelsUp()
Dim objPt As Point
For Each objPt In ActiveChart.SeriesCollection(3).Points
objPt.DataLabel.Top = 52
Next
End Sub
--
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).DataLabe l.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