Rawce
12/19/2006 1:36:00 PM
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" <woth77@yahoo.co.uk> wrote in message
> news:1166529525.800665.90050@79g2000cws.googlegroups.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.HorizontalAlignment = 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.
> >