How to identify Chart Source data as range?

[David]

I have a 'normal' excel chart on a Chart sheet (Cht1)
Source data is coming from 1 of 3 pivot tables all on the same worksheet
(wks1) using vba code.
Userform on chart sheet has 3 option buttons to pass a pivot table name to
'updateChart' code.
All working OK
When the user first opens the file I would like the appropriate option
button to be set to True according to which pivot table the source data was
coming from on the previous fileSave and now being displayed as the active
chart.
Would like to identify the chart source data rngCSD range in code and test
with:
set rng = intersection(wks.PivotTables(pt1).DataBodyRange, rngCSD)
if not rng is nothing then OptBtn1 = True, etc
How do I identify the source data range (in code)
Thanks


then test

 

[Gary Brown]

Hopefully these two pieces of information can point you in the right
direction...

The Chart Source:
ActiveChart.SeriesCollection(1).Formula = _
"=SERIES('Test'!C1,'Test'!A2:A387,'Test'!C2:C387,1)"

The Pivot Table Source:
Activesheet.PivotTables(i).TableRange2.Address

 

[David]

Thanks Gary (i made sure to click the 'Yes' button)
Here's my new code making use of your advice:

Sub GETptNm()
Dim txt As String
Dim SourceRng As Range
Dim ptRng As Range
Dim rng As Range
Dim pt As PivotTable
Dim i As Integer
Dim FirstComma As Integer
Dim n As Integer

'// get cell address for SERIES source data range within Pivot table
'// from seriesCollection(1).Formula
'// example: "=SERIES(PTs!$B$7,PTs!$A$8:$A$52,PTs!$B$8:$B$52,1)"
txt = ActiveChart.SeriesCollection(1).Formula
txt = Replace(txt, "=SERIES(", "")
FirstComma = InStr(txt, ",")
txt = Left(txt, FirstComma - 1) ' range address now identified

'// Capture the range to enclose entire Pivot table
Set SourceRng = Range(txt).CurrentRegion

'// Test identified cell location against each pivot table
n = PTs.PivotTables.Count '// 'PTs' is code name for sheet
For i = 1 To n
Set pt = PTs.PivotTables(i)
Set ptRng = pt.TableRange2
Set rng = Intersect(SourceRng, ptRng)
If Not rng Is Nothing Then
ptNm = pt.Name
Exit For
End If
Next i
End Sub

 

[p45cal]

Just a note of caution; you've taken the first element in the serie
formula which is the Name of the series, often a single cell, but mor
importantly, it can be nothing, the same goes for the second elemen
which are x axis categories/values, the third element is the y value
range, which may be the one to go for because a series really shoul
have some y-values! Some charts have non-contiguous ranges for y-values
so there may be more uncertainty there.

There's more on more reliably finding a chart's source range (includin
a function) in John Walkenbach's book Excel 2002 Power Programming wit
VBA, on page 542, which is surprisingly, but happily, available on th
web 'here' (http://tinyurl.com/y24qhol). It took a minute or two to loa
- I hope it's legal.


Thanks Gary (i made sure to click the 'Yes' button)

 

[David]

Thanks p,
I'll take a look at that

Just a note of caution; you've taken the first element in the series
formula which is the Name of the series, often a single cell, but more
importantly, it can be nothing, the same goes for the second element
which are x axis categories/values, the third element is the y values
range, which may be the one to go for because a series really should
have some y-values! Some charts have non-contiguous ranges for y-values,
so there may be more uncertainty there.

There's more on more reliably finding a chart's source range (including
a function) in John Walkenbach's book Excel 2002 Power Programming with
VBA, on page 542, which is surprisingly, but happily, available on the
web 'here' (http://tinyurl.com/y24qhol). It took a minute or two to load
- I hope it's legal.



Thanks Gary (i made sure to click the 'Yes' button)


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=196879

http://www.thecodecage.com/forumz

.