Hi,

I can't seem to nut this one out. How do I enquire as to the value of
a SeriesCollection Point? I'm trying to vary the color of a datapoint
based on its value, which I'll be able to do if I can get the value.
I'd rather not look at the Source Data array for the chart if I don't
have to.

Anyone know?

This doesn't work:
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.SeriesCollection(1).Points(2).Select
MsgBox ActiveChart.SeriesCollection(1).XValues

Regards

Greg

Hi Greg,

Try something like this -

Sub test()
Dim cht As Chart
Dim sr As Series
Dim arrXvalues As Variant
Dim arrYvalues As Variant

Set cht = ActiveSheet.ChartObjects("Chart 3").Chart
Set sr = cht.SeriesCollection(1)

MsgBox sr.Values(2), , sr.XValues(2)

arrYvalues = sr.Values
arrXvalues = sr.XValues

MsgBox arrYvalues(2), , arrXvalues(2)

End Sub

Regards,
Peter T

"Greg Glynn" wrote in message
ups.com...
Hi,

I can't seem to nut this one out. How do I enquire as to the value of
a SeriesCollection Point? I'm trying to vary the color of a datapoint
based on its value, which I'll be able to do if I can get the value.
I'd rather not look at the Source Data array for the chart if I don't
have to.

Anyone know?

This doesn't work:
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.SeriesCollection(1).Points(2).Select
MsgBox ActiveChart.SeriesCollection(1).XValues

Regards

Greg

Works Great Peter, Thanks.

By the way ... How do I loop through all charts on a particular
worksheet?


Greg

Sub Test()
Dim i As Long, j As Long
Dim ws As Worksheet
Dim cht As Chart
Dim sr As Series
Set ws = ActiveSheet

For i = 1 To ws.ChartObjects.Count
Set cht = ws.ChartObjects(i).Chart
For Each sr In cht.SeriesCollection
Debug.Print sr.Name
'code
Next
Next
End Sub

Instead of For i = 1 To ws.ChartObjects.Count you could do

Dim chtObj as ChartObject
For each chtObj in ws.ChartObjects
set cht = chtObj.Chart

However in rare scenarios there can be problems with this method of looping
each 'object'

Regards,
Peter T

"Greg Glynn" wrote in message
ps.com...
Works Great Peter, Thanks.

By the way ... How do I loop through all charts on a particular
worksheet?


Greg

Peter -

I invariably use

For each chtObj in ws.ChartObjects

What kind of problems have you encountered with this approach?

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


"Peter T" <peter_t@discussions wrote in message
...
Sub Test()
Dim i As Long, j As Long
Dim ws As Worksheet
Dim cht As Chart
Dim sr As Series
Set ws = ActiveSheet

For i = 1 To ws.ChartObjects.Count
Set cht = ws.ChartObjects(i).Chart
For Each sr In cht.SeriesCollection
Debug.Print sr.Name
'code
Next
Next
End Sub

Instead of For i = 1 To ws.ChartObjects.Count you could do

Dim chtObj as ChartObject
For each chtObj in ws.ChartObjects
set cht = chtObj.Chart

However in rare scenarios there can be problems with this method of
looping
each 'object'

Regards,
Peter T

"Greg Glynn" wrote in message
ps.com...
Works Great Peter, Thanks.

By the way ... How do I loop through all charts on a particular
worksheet?


Greg

Hi Jon,

One problem using For Each at the DrawingObject level (ChartObject,
Rectangle etc) is if the object's name includes punctuation it's not picked
up, eg

Sub test2()
Dim i As Long, nCnt As Long
Dim chtObj As ChartObject

For i = 0 To 2
ActiveSheet.ChartObjects.Add(10#, 10# + i * 120, 120#, 80#).Name _
= "MyChart." & i
Next

nCnt = 0
For Each chtObj In ActiveSheet.ChartObjects
nCnt = nCnt + 1
Next
Debug.Print nCnt; " Charts returned with For Each"

nCnt = 0
For i = 1 To ActiveSheet.ChartObjects.Count
nCnt = nCnt + 1
Next
Debug.Print nCnt; " Charts returned with 1 to Count"

End Sub


I'm sure I've also been caught out by one or two other things using 'For
Each' but off the top of my head can't remember what (I did say rare
scenarios). Actually, even For Each sr in .SeriesCollection can be less
reliable than For 1 to .Count with series in certain types of charts.

Regards,
Peter T



"Jon Peltier" wrote in message
...
Peter -

I invariably use

For each chtObj in ws.ChartObjects

What kind of problems have you encountered with this approach?

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


"Peter T" <peter_t@discussions wrote in message
...
Sub Test()
Dim i As Long, j As Long
Dim ws As Worksheet
Dim cht As Chart
Dim sr As Series
Set ws = ActiveSheet

For i = 1 To ws.ChartObjects.Count
Set cht = ws.ChartObjects(i).Chart
For Each sr In cht.SeriesCollection
Debug.Print sr.Name
'code
Next
Next
End Sub

Instead of For i = 1 To ws.ChartObjects.Count you could do

Dim chtObj as ChartObject
For each chtObj in ws.ChartObjects
set cht = chtObj.Chart

However in rare scenarios there can be problems with this method of
looping
each 'object'

Regards,
Peter T

"Greg Glynn" wrote in message
ps.com...
Works Great Peter, Thanks.

By the way ... How do I loop through all charts on a particular
worksheet?


Greg

I remember now the discussion about punctuation in object names, and I am
glad I never got into that habit.

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


"Peter T" <peter_t@discussions wrote in message
...
Hi Jon,

One problem using For Each at the DrawingObject level (ChartObject,
Rectangle etc) is if the object's name includes punctuation it's not
picked
up, eg

Sub test2()
Dim i As Long, nCnt As Long
Dim chtObj As ChartObject

For i = 0 To 2
ActiveSheet.ChartObjects.Add(10#, 10# + i * 120, 120#, 80#).Name _
= "MyChart." & i
Next

nCnt = 0
For Each chtObj In ActiveSheet.ChartObjects
nCnt = nCnt + 1
Next
Debug.Print nCnt; " Charts returned with For Each"

nCnt = 0
For i = 1 To ActiveSheet.ChartObjects.Count
nCnt = nCnt + 1
Next
Debug.Print nCnt; " Charts returned with 1 to Count"

End Sub


I'm sure I've also been caught out by one or two other things using 'For
Each' but off the top of my head can't remember what (I did say rare
scenarios). Actually, even For Each sr in .SeriesCollection can be less
reliable than For 1 to .Count with series in certain types of charts.

Regards,
Peter T



"Jon Peltier" wrote in message
...
Peter -

I invariably use

For each chtObj in ws.ChartObjects

What kind of problems have you encountered with this approach?

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


"Peter T" <peter_t@discussions wrote in message
...
Sub Test()
Dim i As Long, j As Long
Dim ws As Worksheet
Dim cht As Chart
Dim sr As Series
Set ws = ActiveSheet

For i = 1 To ws.ChartObjects.Count
Set cht = ws.ChartObjects(i).Chart
For Each sr In cht.SeriesCollection
Debug.Print sr.Name
'code
Next
Next
End Sub

Instead of For i = 1 To ws.ChartObjects.Count you could do

Dim chtObj as ChartObject
For each chtObj in ws.ChartObjects
set cht = chtObj.Chart

However in rare scenarios there can be problems with this method of
looping
each 'object'

Regards,
Peter T

"Greg Glynn" wrote in message
ps.com...
Works Great Peter, Thanks.

By the way ... How do I loop through all charts on a particular
worksheet?


Greg

Sure, but might need to cater for other people's habits!

Regards,
Peter T

"Jon Peltier" wrote in message
...
I remember now the discussion about punctuation in object names, and I am
glad I never got into that habit.

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


"Peter T" <peter_t@discussions wrote in message
...
Hi Jon,

One problem using For Each at the DrawingObject level (ChartObject,
Rectangle etc) is if the object's name includes punctuation it's not
picked
up, eg
<snip

Regards,
Peter T



"Jon Peltier" wrote in message
...
Peter -

I invariably use

For each chtObj in ws.ChartObjects

What kind of problems have you encountered with this approach?

- Jon
-------