XY Chart Label - second request for help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AK - 28 Dec 2007 17:32 GMT

Need the help up of the Excel gurus..

I'm using Rob Bovey's XY Chart labeler to label a Red, Yellow, and Green XY
chart.

By the way...this add-in is great!!!

The data for the charts are built from dynamic named ranges for both X and
Y. I have a helper column for the labels that is a dynamic named range as
well. All data is linked to an MS Access db.

Here's what I'm trying to do:
The data for the charts will change whenever I load the file with new data
and was wondering if anyone knew how to create a macro to call up the Add In
and place the dynamic named range in the "... Label Range" field for each
Red, Yellow, and Green data series.

I would then repeat this macro for each XY chart in the file.

Many many thanks in advance.

AK

Reply to this Message

Jon Peltier - 28 Dec 2007 17:40 GMT

AFAIK, Rob's utility doesn't have convenient hooks for external VBA routines
to use. I've frequently rolled my own routine to apply labels. This tends to
be smaller than Rob's without as much error handling, and is specific to the
particular solution I'm working on. Essentially it finds the range based on
whatever VBA or Name/Refers To definitions you use, and goes cell by cell to
apply the labels and if desired the formats.

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

> Need the help up of the Excel gurus..
>
[quoted text clipped - 20 lines]
>
> AK

Reply to this Message

AK - 28 Dec 2007 17:59 GMT

Hi Jon:

Thanks. What is "AFAIK"?

Could I trouble you for the code I'd use for a dynamic named range named
"GreenDots"?

Thanks in advance,

> AFAIK, Rob's utility doesn't have convenient hooks for external VBA routines
> to use. I've frequently rolled my own routine to apply labels. This tends to
[quoted text clipped - 34 lines]
> >
> > AK

Reply to this Message

Jon Peltier - 28 Dec 2007 18:48 GMT

AFAIK = as far as I know

You would do something like this (caution: air code)

Sub LabelSeriesOne()
Dim srs As Series
Dim iPoint As Long
Dim rLabels As Range

Set srs = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
Set rLabels = ActiveSheet.Range("GreenDots")
For iPoint = 1 To srs.Points.Count
srs.Points(iPoint).HasDataLabel = True
srs.Points(iPoint).DataLabel.Characters.Text =
rLabels.Cells(iPoint).Value
Next
End Sub

You can also get the font format of the cell and apply it to the label,
which Rob's utility does.

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

> Hi Jon:
>
[quoted text clipped - 54 lines]
>> >
>> > AK

Reply to this Message

AK - 28 Dec 2007 19:21 GMT

Hi Jon:

The code errors at

srs.Points(iPoint).HasDataLabel = True

Would you know what the issue is?

THanks,

> AFAIK = as far as I know
>
[quoted text clipped - 82 lines]
> >> >
> >> > AK

Reply to this Message

Jon Peltier - 28 Dec 2007 19:33 GMT

It worked on a simple 3-point series for me. Is the value of this point
#N/A? Try this refinement:

Sub LabelSeriesOne()
Dim srs As Series
Dim iPoint As Long
Dim rLabels As Range

Set srs = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
Set rLabels = ActiveSheet.Range("GreenDots")
For iPoint = 1 To srs.Points.Count
If IsNumeric(srs.Values(iPoint)) Then
srs.Points(iPoint).HasDataLabel = True
srs.Points(iPoint).DataLabel.Characters.Text =
rLabels.Cells(iPoint).Value
End If
Next
End Sub

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

> Hi Jon:
>
[quoted text clipped - 101 lines]
>> >> >
>> >> > AK

Reply to this Message