Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to identify Chart Source data as range?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to identify Chart Source data as range?
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 -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "David" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to identify Chart Source data as range?
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 "Gary Brown" wrote: 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 -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "David" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to identify Chart Source data as range?
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. David;706130 Wrote: 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 *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=196879 http://www.thecodecage.com/forumz |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to identify Chart Source data as range?
Thanks p,
I'll take a look at that "p45cal" wrote: 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. David;706130 Wrote: 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 *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=196879 http://www.thecodecage.com/forumz . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a named range as a data source for a chart | Charts and Charting in Excel | |||
Using a Named Range for Data Source In A Chart | Charts and Charting in Excel | |||
Chart: Use range name for source data | Excel Programming | |||
Chart source data range | Excel Programming | |||
How to identify source or location of MAX_VALUE in range | Excel Worksheet Functions |