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.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,611
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,504
Messages
5,832,106
Members
430,111
Latest member
Francis Xavier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top