How can I programmatically get the name of the worksheet where the data source of a chart is?

[DF10]

Hi all,
I want to programmatically add a title to a chart.
The chart is on a chart sheet (what happens when you hit F11), not on the same sheet where the data source is.
The title text is on cell A1 of the worksheet with the datasource.

Now if the data source is on the sheet "data" then this works

    ActiveChart.HasTitle = True
    ActiveChart.ChartTitle.Text = "=data!A1"

However I do not the sheet name (nor the position in the workbook)

I was hoping something like ActiveChart.GetSourceData exists (as opposed to ActiveChart.SetSourceData).

How can identify the worksheet where the data source of my chart is

something like

mysheet =  ActiveChart.GetSourceData ...
and then 
ActiveChart.ChartTitle.Text = "=mysheet!A1"

Thanks
DF

 

[Andy Pope]

You need to parse out the sheet reference from 1 of the series formula

You can try something like this.

activechart.charttitle.text = "=" & split(split(activechart.SeriesCollection(1).formula,",")(1),"!")(0) & "!A1"

 

[DF10]

That works a treat.
Thanks heaps Andy
DF