Thanks, Andy! I tried your first suggestion, and it worked great.

-- David

"Andy Pope" wrote in message
...
Hi,

You can either write all the values to a variant array and then index that
or use the Index function.

Dim vntData As Variant

vntData = ActiveChart.SeriesCollection(1).Values
MsgBox "Point 3=" & vntData(3)

MsgBox "Point 3=" & Application.WorksheetFunction.Index( _
ActiveChart.SeriesCollection(1).Values, 3)


Cheers
Andy


David Benson wrote:
I have a somewhat complex set of bar-charts. Some (but not all) of the
data series that make up each chart have the values of the points
displayed. Due to the complex formatting of the charts, I can't just
display the values using Data Labels, so I display them in Text Boxes. I
have written a macro that automatically positions each text box just
above the bar whose value it shows, and changes the formatting of the
text box based on the formatting of whatever it happens to be positioned
over.

Until now, I got the value associated with each bar from the actual cells
on the worksheet. However, a recent change to the requirements for the
charts means I am not always plotting data from a contiguous range. For
example, a data range might consist of Sheet2!(E2,E7:E10) on one chart,
or Sheet 3!(E3:E9) on another chart. This makes looking up the value for
each bar in the chart-formatting macro harder.

I would like to be able to use the set of plotted values rather than
reading cells off the worksheet, but I can't figure out how to get at
them. I tried using ActiveChart.SeriesCollection(i).Points(j), but there
is no Values property associated with this object. I also tried using
ActiveChart.SeriesCollection(i).Values, but that apparently only returns
the array of plotted values -- I can't get it to return a single value.

Does anyone know how I can return the plotted y-value of a single point?

BTW - I am using Excel 2007. As a side comment, it appears to me that
the Help function in this version of VBA is seriously degraded from what
it used to be in Excel 2003!


David Benson

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info