XL 2003 Graphs: Retrieving Point Name

[Timmy!]

HI folks,

I'm an Access developer and my current app manipulates MS Graph
generated charts in Access. To determine code needed, I record Excel
macros, examine the resultant VBA coding and adapt it for my Access
modules.

What I'm looking to get is what one sees in controltip text when you
mouse over a column in, for example, a column chart. For example, the
controltiptext (pop up) might show:

Series "Total Costs" Point "Labour"
Value: 128423.73

I've googled the subject and combed through the object browser and have
found partial answers, but not exactly what I'm looking for.
http://tinyurl.com/kcmwx helped me figure out the series name (after I
figured out that I had to dim sr as series), but I'm still stuck on
getting the point name. For the series' names I'm able to use:

sub sSeriesNames

Dim i As Integer
Dim sr As Series

For i = 1 To ActiveSheet.ChartObjects.Count
MsgBox ActiveSheet.ChartObjects(i).Name
For Each sr In ActiveSheet.ChartObjects(i).Chart.SeriesCollection
MsgBox sr.Name
Next
Next i

End Sub


And this will display regardless of whatever has been set in the
arguments of ApplyDataLabels.

Now, how to do similar for point name (and value, for that matter)?

TIA, any help will be much appreciated.

PS, my regular news server doesn't carry this NG. Anyone know how to
change the email address that appears from Google Groups postings?

--
Tim Marshall

[Jon Peltier]

The .XValues and .Values properties of a series return arrays containing the
X and Y values of the points in the series. It goes something like this:

sub sPointInfo()

Dim iCht As Integer
Dim sr As Series
Dim iPt as Long
dim vXVals as Variant
Dim vYVals as Variant

For iCht = 1 To ActiveSheet.ChartObjects.Count
MsgBox ActiveSheet.ChartObjects(iCht).Name
For Each sr In ActiveSheet.ChartObjects(iCht).Chart.SeriesCollection
' you probably don't want to use message boxes....
MsgBox sr.Name
vXVals = sr.XValues
vYVals = sr.Values
For iPt = 1 to sr.Points.Count
' you definitely don't want to use message boxes....
MsgBox "X = " & vXVals(iPt) & ", Y = " & vYVals(iPt)
Next
Next
Next i

End Sub

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


"Timmy!" <[email protected]> wrote in message
news:[email protected]...
> HI folks,
>
> I'm an Access developer and my current app manipulates MS Graph
> generated charts in Access. To determine code needed, I record Excel
> macros, examine the resultant VBA coding and adapt it for my Access
> modules.
>
> What I'm looking to get is what one sees in controltip text when you
> mouse over a column in, for example, a column chart. For example, the
> controltiptext (pop up) might show:
>
> Series "Total Costs" Point "Labour"
> Value: 128423.73
>
> I've googled the subject and combed through the object browser and have
> found partial answers, but not exactly what I'm looking for.
> http://tinyurl.com/kcmwx helped me figure out the series name (after I
> figured out that I had to dim sr as series), but I'm still stuck on
> getting the point name. For the series' names I'm able to use:
>
> sub sSeriesNames
>
> Dim i As Integer
> Dim sr As Series
>
> For i = 1 To ActiveSheet.ChartObjects.Count
> MsgBox ActiveSheet.ChartObjects(i).Name
> For Each sr In ActiveSheet.ChartObjects(i).Chart.SeriesCollection
> MsgBox sr.Name
> Next
> Next i
>
> End Sub
>
>
> And this will display regardless of whatever has been set in the
> arguments of ApplyDataLabels.
>
> Now, how to do similar for point name (and value, for that matter)?
>
> TIA, any help will be much appreciated.
>
> PS, my regular news server doesn't carry this NG. Anyone know how to
> change the email address that appears from Google Groups postings?
>
> --
> Tim Marshall
>