'Use a SpinButton instead of a ScrollBar.
'Set the SpinButton Max value to a large number.
'Set the SpinButton Min value to a large negative number.
'Adjust names in code as necessary.
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
'Place this code in the worksheet module...
Private Sub SpinButton1_SpinDown()
Call LooksLikeScrollDown
End Sub
'--
Private Sub SpinButton1_SpinUp()
Call LooksLikeScrollUp
End Sub
'----------
'Place this code in a standard module...
Sub LooksLikeScrollUp()
'Jim Cone - San Francisco - April 2007
Dim rng As Excel.Range
Dim vFirst As Variant
Dim lngCnt As Long
Set rng = Range("A45:A105")
lngCnt = rng.Count
vFirst = rng(1).Value
rng.Resize(lngCnt - 1, 1).Value = rng.Offset(1, 0).Resize(lngCnt - 1, 1).Value
rng(lngCnt).Value = vFirst
Set rng = Nothing
End Sub
'--
Sub LooksLikeScrollDown()
Dim rng As Excel.Range
Dim vLast As Variant
Dim lngCnt As Long
Set rng = Range("A45:A105")
lngCnt = rng.Count
vLast = rng(lngCnt).Value
Range(rng(2), rng(lngCnt)).Value = rng.Resize(lngCnt - 1, 1).Value
rng(1).Value = vLast
Set rng = Nothing
End Sub
'----------
<
[email protected]>
wrote in message
Hi,
How can I make a scrollbar control a number of cells in a worksheet. I
want Cells A45: A105 to be controlled via the control box scrollbar
which I just placed on my worksheet. Could anyone please help me with
how I could do that.
Thank You. All help is appreciated.