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

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
_______


"AK" wrote in message
...
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

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,

"Jon Peltier" wrote:

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
_______


"AK" wrote in message
...
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

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
_______


"AK" wrote in message
...
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,

"Jon Peltier" wrote:

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
_______


"AK" wrote in message
...
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

Hi Jon:

The code errors at

srs.Points(iPoint).HasDataLabel = True

Would you know what the issue is?

THanks,


"Jon Peltier" wrote:

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
_______


"AK" wrote in message
...
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,

"Jon Peltier" wrote:

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
_______


"AK" wrote in message
...
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

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
_______


"AK" wrote in message
...
Hi Jon:

The code errors at

srs.Points(iPoint).HasDataLabel = True

Would you know what the issue is?

THanks,


"Jon Peltier" wrote:

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
_______


"AK" wrote in message
...
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,

"Jon Peltier" wrote:

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
_______


"AK" wrote in message
...
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