Modifying Chart Series Range
Modifying Chart Series Range
(OP)
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
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





RE: Modifying Chart Series Range
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 = "=SERIES(""" & SeriesName & """," & XRange & "," & YRange & ", 1)"
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.
RE: Modifying Chart Series Range
Works like a charm. Thanks.
-Mike
RE: Modifying Chart Series Range