Question to do with scroll bars

[Godleh]

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.

 

[Jaafar Tribak]

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]

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