Macro to add labels to data points in an xy scatter chart

[Scott Wagner]

I have searched these boards, and am still without a good solution. Hoping
you can help.

I am aware of add in programs that will achieve this result, but I don't
have that option because of the number of users I have, and permissions to
install on our network. (sucks) I found this item in the microsoft support
pages and am trying to apply the macro in the example.

http://support.microsoft.com/default.aspx?scid=kb;en-us;213750

Everything works fine, the lables attach to the data points as expected, but
I get "Run-time error '1004': Unable to set the HasDataLabel property of
the Point class" when I run the macro. I click end, and the data points show
up.

I don't want to release this spreasheet to users until this issue is worked
out. Any ideas why this is happening, or do you know how to close that run
time error window with VBA?

Thanks so much,

Scott Wagner

 

[Jon Peltier]

For some reason, does the particular point that crashes not appear in the
chart, because it's not a plottable value? Such as #N/A or a blank? Since
the point isn't plotted, you can't apply a data label.

You could insert some error evasion code:

For Counter = 1 To Range(xVals).Cells.Count
On Error Resume Next
ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _
True
ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
On Error Goto 0
Next Counter

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Scott Wagner]

That works perfectly!

Thanks!

For some reason, does the particular point that crashes not appear in the
chart, because it's not a plottable value? Such as #N/A or a blank? Since
the point isn't plotted, you can't apply a data label.

You could insert some error evasion code:

For Counter = 1 To Range(xVals).Cells.Count
On Error Resume Next
ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _
True
ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
On Error Goto 0
Next Counter

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______