Modifying Chart Series Range 2

[Vidaman]

I have a chart where I would like to control the range of values to be plotted by changing the range value in a cell. That is, one time I may want to plot all 100 points of a series, next I might want only to plot 50 of them.

Right now I manually adjust the series range by going into the "Source Data.." controls. I would like to tie the range to a cell value instead.

If you can actually understand my question, your help will certainly be appreciated.

Thanks

-Vidaman

 

[joerd]

Well, with a few assumptions, here is my contribution:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim SeriesName As String, XRange As String, YRange As String
Dim MinX As Integer, MaxX As Integer

'First value in row identified in cell E1
'Last value in row identified in cell E2
'Macro to run if the user changes cell E1 or E2
If (Target.Address = "$E$1") Or (Target.Address = "$E$2") Then
'Check input and correct
    If IsNumeric([E1].Value) Then MinX = [E1].Value Else MinX = 1
    If IsNumeric([E2].Value) Then MaxX = [E2].Value Else MaxX = 1
    If (MinX < 1) Then MinX = 1
    If (MaxX < 1) Then MaxX = 1
'Xrange is in the A column (column number 1).
    XRange = Range(Cells(MinX, 1), Cells(MaxX, 1)).Address(External:=True)
'Yrange is in the B column (column number 2).
    YRange = Range(Cells(MinX, 2), Cells(MaxX, 2)).Address(External:=True)
'This only changes Series 1 source data (in chart 1, assumed to be embedded in the spreadsheet
    With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
       SeriesName = .Name
       .Formula = &quot;=SERIES(&quot;&quot;&quot; & SeriesName & &quot;&quot;&quot;,&quot; & XRange & &quot;,&quot; & YRange & &quot;, 1)&quot;
    End With
End If
End Sub

Put it in the worksheet code section, and it should work whenever you change cell [E1] or [E2]

Good luck ;-)

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[Vidaman]

Joerd,

Works like a charm. Thanks.

-Mike

 

[Manher]

Thanks very much, was looking for something like this for ages. Works great.