How can I ask a chart where its data comes from?

C

Chrisso

Hi there,

How can I ask a chart where its data comes from? Ideally I would like
to get the range back that is represented by a point on the chart. I
at least need to know which worksheet the data is on.

Is this possible? I cannot see how using Excel VB help.

Cheers for any ideas,
Chrisso
 
S

smartin

Chrisso said:
Hi there,

How can I ask a chart where its data comes from? Ideally I would like
to get the range back that is represented by a point on the chart. I
at least need to know which worksheet the data is on.

Is this possible? I cannot see how using Excel VB help.

Cheers for any ideas,
Click to expand...

Hi Chrisso,

In Excel 2003 and prior, right click the chart area and inspect "Source
Data". Not sure if this is the same in 2007.
 
C

Chrisso

Thanks Andy.

I came up with this grubby but effective (so far) code:

Dim sFormulae As String
sFormulae = chtEvent.SeriesCollection(1).FormulaR1C1
' worksheets can appear in the formulae in two forms:
' "=SERIES(Data!R1C2,Data!R2C1:R10C1,Data!R2C2:R10C2)
' "=SERIES('My Data'!R1C2,Data!R2C1:R10C1,Data!R2C2:R10C2)

If InStr(sFormulae, "=SERIES('") > 0 Then
' sheet name is enclosed in single quotes:
Debug.Print "[" & Mid$(sFormulae, Len("=SERIES('") + 1, InStr
(sFormulae, "'!") - Len("=SERIES('") - 1) & "]"
Else
' sheet name is NOT enclosed in single quotes:
Debug.Print "[" & Mid$(sFormulae, Len("=SERIES(") + 1, InStr
(sFormulae, "!") - Len("=SERIES(") - 1) & "]"
End If


Chrisso
 
Top