Hello All,

I'm trying to programmatically add data labels to a chart that is also
created programmatically. I've used the following to go through the
SeriesCollection(iSeries) added elsewhere in the subroutine:

Dim ptsPoint As Points
Dim ptsCount As Integer
Dim iCountPoints As Integer
Set ptsPoint = chartMyChart.SeriesCollection(iSeries).Points
ptsCount = WorksheetFunction.CountA(Sheets("Results").Columns (1))
For iCountPoints = 1 To ptsCount
ptsPoint(iCountPoints).HasDataLabel = True
ptsPoint(iCountPoints).ApplyDataLabels Type:=xlDataLabelsShowValue
ptsPoint(iCountPoints).DataLabel.HorizontalAlignme nt = xlLeft
ptsPoint(iCountPoints).DataLabel.VerticalAlignment = xlTop
ptsPoint(iCountPoints).DataLabel.Position = xlLabelPositionBelow
ptsPoint(iCountPoints).DataLabel.Orientation = xlUpward
ptsPoint(iCountPoints).DataLabel.Font.Size = 6
ptsPoint(iCountPoints).DataLabel.Text =
Sheets("Results").Cells(iCountPoints, 1).Value
ptsPoint(iCountPoints).DataLabel.ShowValue = True
iCountPoints = iCountPoints + 1
Next

This works fine but the chart only shows the even data labels. So, if
there were five points based on the values in column A of the Results
sheet that read:

Monday
Tuesday
Wednesday
Thursday
Friday

Only Monday, Wednesday and Friday would be shown on the chart itself.
I've tried reducing the font or just temporarily putting a "." instead
of the values in column A (so the chart is less cluttered), but that
doesn't seem to make a difference. I've tried forcing it using the
..DataLabel.ShowValue = True, but that hasn't made any difference
either. Any advice?

Many thanks for any help you can offer.

Cheers,

Ross.

Hi Ross,

I haven't tried your code but suggest you remove this line
iCountPoints = iCountPoints + 1

In passing you could probably apply DataLabels to the entire series in one
go with your formats, then loop the Datalabels applying your text up until
you get to ptsCount, then delete any further labels (if any).

Regards,
Peter T

"Rawce" wrote in message
ps.com...
Hello All,

I'm trying to programmatically add data labels to a chart that is also
created programmatically. I've used the following to go through the
SeriesCollection(iSeries) added elsewhere in the subroutine:

Dim ptsPoint As Points
Dim ptsCount As Integer
Dim iCountPoints As Integer
Set ptsPoint = chartMyChart.SeriesCollection(iSeries).Points
ptsCount = WorksheetFunction.CountA(Sheets("Results").Columns (1))
For iCountPoints = 1 To ptsCount
ptsPoint(iCountPoints).HasDataLabel = True
ptsPoint(iCountPoints).ApplyDataLabels Type:=xlDataLabelsShowValue
ptsPoint(iCountPoints).DataLabel.HorizontalAlignme nt = xlLeft
ptsPoint(iCountPoints).DataLabel.VerticalAlignment = xlTop
ptsPoint(iCountPoints).DataLabel.Position = xlLabelPositionBelow
ptsPoint(iCountPoints).DataLabel.Orientation = xlUpward
ptsPoint(iCountPoints).DataLabel.Font.Size = 6
ptsPoint(iCountPoints).DataLabel.Text =
Sheets("Results").Cells(iCountPoints, 1).Value
ptsPoint(iCountPoints).DataLabel.ShowValue = True
iCountPoints = iCountPoints + 1
Next

This works fine but the chart only shows the even data labels. So, if
there were five points based on the values in column A of the Results
sheet that read:

Monday
Tuesday
Wednesday
Thursday
Friday

Only Monday, Wednesday and Friday would be shown on the chart itself.
I've tried reducing the font or just temporarily putting a "." instead
of the values in column A (so the chart is less cluttered), but that
doesn't seem to make a difference. I've tried forcing it using the
.DataLabel.ShowValue = True, but that hasn't made any difference
either. Any advice?

Many thanks for any help you can offer.

Cheers,

Ross.

Hah hah! I'm such a numpty. I'm getting my loops all messed up. You're
entirely right, the For is looping with the Next anyway, so I'm
actually double incrementing - hence the even labels applying only.
Sorry, I thought it was some quirk with Excel and how it plots charts,
not me being used to using Do Whiles!

Humble apologies and many thanks,

Ross.

Peter T wrote:
Hi Ross,

I haven't tried your code but suggest you remove this line
iCountPoints = iCountPoints + 1

In passing you could probably apply DataLabels to the entire series in one
go with your formats, then loop the Datalabels applying your text up until
you get to ptsCount, then delete any further labels (if any).

Regards,
Peter T

"Rawce" wrote in message
ps.com...
Hello All,

I'm trying to programmatically add data labels to a chart that is also
created programmatically. I've used the following to go through the
SeriesCollection(iSeries) added elsewhere in the subroutine:

Dim ptsPoint As Points
Dim ptsCount As Integer
Dim iCountPoints As Integer
Set ptsPoint = chartMyChart.SeriesCollection(iSeries).Points
ptsCount = WorksheetFunction.CountA(Sheets("Results").Columns (1))
For iCountPoints = 1 To ptsCount
ptsPoint(iCountPoints).HasDataLabel = True
ptsPoint(iCountPoints).ApplyDataLabels Type:=xlDataLabelsShowValue
ptsPoint(iCountPoints).DataLabel.HorizontalAlignme nt = xlLeft
ptsPoint(iCountPoints).DataLabel.VerticalAlignment = xlTop
ptsPoint(iCountPoints).DataLabel.Position = xlLabelPositionBelow
ptsPoint(iCountPoints).DataLabel.Orientation = xlUpward
ptsPoint(iCountPoints).DataLabel.Font.Size = 6
ptsPoint(iCountPoints).DataLabel.Text =
Sheets("Results").Cells(iCountPoints, 1).Value
ptsPoint(iCountPoints).DataLabel.ShowValue = True
iCountPoints = iCountPoints + 1
Next

This works fine but the chart only shows the even data labels. So, if
there were five points based on the values in column A of the Results
sheet that read:

Monday
Tuesday
Wednesday
Thursday
Friday

Only Monday, Wednesday and Friday would be shown on the chart itself.
I've tried reducing the font or just temporarily putting a "." instead
of the values in column A (so the chart is less cluttered), but that
doesn't seem to make a difference. I've tried forcing it using the
.DataLabel.ShowValue = True, but that hasn't made any difference
either. Any advice?

Many thanks for any help you can offer.

Cheers,

Ross.