×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Modifying Chart Series Range
2

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

RE: Modifying Chart Series Range

2
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 = "=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

(OP)
Joerd,

Works like a charm.  Thanks.

-Mike

RE: Modifying Chart Series Range

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources