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

[Chrisso]

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

 

[Jon Peltier]

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