Run-Time Chart Only Displays Even DataLabels
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. |
Run-Time Chart Only Displays Even DataLabels
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. |
Run-Time Chart Only Displays Even DataLabels
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. |
All times are GMT +1. The time now is 02:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com