Flexible range graph

[angelikv]

5024

Hi Guys!!!!!
I am working on a homework but I don't know how to make a flexible range on a graph.
This is what I have so far,
Looking at the first sheet, at the second graph, which is time against price. the data table for that graph is the second data table to the side of the graphs. As you can see I know how to make a basic XY scatter chart with this data, but I need to make it so that the user can input a different time period in Cells B6/C6 and that can be reflected in the graph. Basically, how do I make the X-range of a graph flexible?

Thank you!!!! :hi:

Angelica

Ps. See attachment

[JimmyTheHand]

Hi Angelica :hi:

Is it good enough if you change only the scaling of Axis X? If so, then try this:
(or see the attachment)

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Me.Range("B6:C6")) Is Nothing Then Exit Sub

Dim Hit As Range
Select Case Target.Address
Case "$B$6"
Set Hit = Me.Range("U13:U63").Find(Target, , xlValues)
If Hit Is Nothing Then
MsgBox ("invalid value")
Exit Sub
End If
With ActiveSheet.ChartObjects("Diagram 3").Chart.Axes(xlCategory)
.MinimumScale = Hit.Offset(, 1)
End With
Case "$C$6"
Set Hit = Me.Range("U13:U63").Find(Target, , xlValues)
If Hit Is Nothing Then
MsgBox ("invalid value")
Exit Sub
End If
With ActiveSheet.ChartObjects("Diagram 3").Chart.Axes(xlCategory)
.MaximumScale = Hit.Offset(, 1)
End With
End Select
End Sub

[mdmackillop]

Hi Angelica,
Under the rules of the forum, we can't offer a solution to your homework, but here's some guidance.
From what you say, you need to have a dynamic data source for your charts, rather than fixed by cell references. Have a look at this post (http://www.vbaexpress.com/forum/showpost.php?p=62109&postcount=10) for one method.
Regards
MD