XY Chart Label - second request for help

[AK]

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

 

[Jon Peltier]

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

 

[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]

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

 

[AK]

Hi Jon:

The code errors at

srs.Points(iPoint).HasDataLabel = True

Would you know what the issue is?

THanks,

 

[Jon Peltier]

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