How can I add scrollbar to a number of cells

R

rachitm

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.
 
J

Jim Cone

'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.
 
G

Guest

Assumed is that the scrollbar is named "ScrollBar1". In the worksheet's code
module paste this:

Private Sub ScrollBar1_Change()
Range("A45:A105").Value = ScrollBar1.Value
End Sub

To access the worksheet's code module, right click the worksheet tab and
select View Code. Then paste the above.

Regards,
Greg
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top