Is it possible to ask a chart which worksheet its data lives on?

Hi All

Is it possible through VBA to ask a chart for the name of the
worksheet where it is sourceing its data?

I have one worksheet with all my charts. Each chart refers to data on
a seperate sheet - one per chart.

I would like to loop through each ActiveSheet.ChartObjects and find
the worksheet where the data lives - I then intend to update it based
on user criteria.

I cannot see how to acheive this through the object model. Is this
possible? I suppose it may not be as a chart may have data from
multiple worksheets.

Thanks for any ideas in advance,
Chrisso

You can parse the series formula of each series to find where its data
resides. John Walkenbach has a class module that does the heavy lifting for
you:

http://www.j-walk.com/ss/excel/tips/tip83.htm

Find the range containing a series' X or Y values, then determine the parent
of that range.

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


"Chrisso" <chris75sutton@gmail.com> wrote in message
news:1187023628.140634.62970@b79g2000hse.googlegroups.com...
> Hi All
>
> Is it possible through VBA to ask a chart for the name of the
> worksheet where it is sourceing its data?
>
> I have one worksheet with all my charts. Each chart refers to data on
> a seperate sheet - one per chart.
>
> I would like to loop through each ActiveSheet.ChartObjects and find
> the worksheet where the data lives - I then intend to update it based
> on user criteria.
>
> I cannot see how to acheive this through the object model. Is this
> possible? I suppose it may not be as a chart may have data from
> multiple worksheets.
>
> Thanks for any ideas in advance,
> Chrisso
>