Difficulty Setting Up Excel Chart With Scroll Capabilities

  • Thread starter Thread starter Dave Marden
  • Start date Start date
D

Dave Marden

I am trying to set up an excel graph that will let me scroll through a large
amount of values. I want to be able to set my range as something like
A1:H501 and actually have data all the way to Row 65000.

What I want to do is then have a scroll bar be manually moved and the chart
update according to the new area I want charted. The Code Below is what I
have tried and I keep getting errors. Any help would be appreciated.

I am grabbing data from an Programmable Logic Controller and the data is
spread over a 2 hour period, so I want to be able to scroll through the
data.

Dim LowerBound As Double
Dim UpperBound As Double
Dim CenterSelection As Double
Dim GraphRange(5000, 32) As Single

Public Sub SBarArea_Change()
GetValues
End Sub

Public Sub GetValues()
Dim OriginalArea As Range
CenterSelection = Range("A46").Value
LowerBound = CenterSelection - 250
UpperBound = CenterSelection + 250
MsgBox "Before Copying"
OriginalArea = Range("D" & LowerBound & ":" & "K" & UpperBound)
Range("M15:T515") = Range(OriginalArea)
MsgBox "After Copying"
LowerBound = 0
UpperBound = 0
End Sub

It Appears that excel doesn't like me making my data in OriginalArea
dynamic.

Any help would be appreciated,
Dave Marden
 
Hi,

Try this revised code.

Public Sub GetValues()
Dim OriginalArea As String
CenterSelection = Range("A46").Value
LowerBound = CenterSelection - 250
UpperBound = CenterSelection + 250
MsgBox "Before Copying"

OriginalArea = _
Range("D" & LowerBound & ":" & "K" & UpperBound).Address
Range("M15:T515") = Range(OriginalArea).Value

MsgBox "After Copying"
LowerBound = 0
UpperBound = 0
End Sub

You might find the Named range approach easiser. No code required.
http://peltiertech.com/Excel/Charts/Dynamics.html

Cheers
Andy
 
Thanks Andy, That was a big help.

Dave


Andy Pope said:
Hi,

Try this revised code.

Public Sub GetValues()
Dim OriginalArea As String
CenterSelection = Range("A46").Value
LowerBound = CenterSelection - 250
UpperBound = CenterSelection + 250
MsgBox "Before Copying"

OriginalArea = _
Range("D" & LowerBound & ":" & "K" & UpperBound).Address
Range("M15:T515") = Range(OriginalArea).Value

MsgBox "After Copying"
LowerBound = 0
UpperBound = 0
End Sub

You might find the Named range approach easiser. No code required.
http://peltiertech.com/Excel/Charts/Dynamics.html

Cheers
Andy
Click to expand...
 
Back
Top