Question to do with scroll bars

Godleh

New Member
Joined
Sep 30, 2005
Messages
21
The scroll bars used to navigate around the sheet take you 1 row down per click and 1 column across per click. Can you change the number of cells that a single click of the scroll bar moves? I want it to be 3.

It would be great if this could be done to act ONLY on 1 sheet too.
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,069
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Unless I am missing something,there is no such scroll event in XL.

Using a Mouse Hook or a Timer might provide a close enough solution.However, this would be code intensive...Worse still, it could have an adverse effect on performance and speed.

And if you weigh this against what you may benefit from such a code, I don't think it is worthwhile.

Regards.
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
I don't know of any Event that would trap just the Worksheet Scroll Bars?

You can do it on a Frame, UserForm or Control Scroll Bar, But I don't think you can do it on a Sheet?

"SmallScroll" is the only good Scroll object and you can tie it to the Select Event to get something close, but it would work all the time even when you select a cell, which would not be what you want!


If you had some type of Event that only detects the Worksheet ScrollBars then something using a conditioned statement using this, could be used, but we don't!

Worksheets("Sheet1").Activate
ActiveWindow.SmallScroll down:=3
ActiveWindow.SmallScroll right:=3

This code will Scroll the screen view to your selection:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Add this to the sheet module for the sheet you want
'the active cell to always be the upper left of the
'screen view!
'Note: Each selection will move you farther away from
'home(A1), so to go back you need to use the scroll bars.
'Else, add code to return you to "A1" from time to time.

Application.Goto Range(Target.Address), scroll:=True
End Sub


And this pair of Subs will turn the Scroll Bars On or Off:


Sub Workbook_Open()
'On workbook open turn off scroll bars.

With ActiveWindow
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
End With

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Turn the scroll bars on before closing the workbook.
With ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,501
Members
412,670
Latest member
Khin Zaw Htwe
Top