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

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...b;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

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" <[email protected]> wrote in message
news:[email protected]...
>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...b;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

That works perfectly!

Thanks!

"Jon Peltier" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> >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...b;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
>
>
>