0

Hard to explain this one. Imagine you're in Excel, pressing the down key to move down through the rows. Once you get to the bottom of the screen, pressing the down key again will cause the spreadsheet to scroll down a row at a time, so that your selected cell is never out of visible range.

Oddly, what I'd like is for this behaviour to happen when I'm (say) 20 rows from the bottom. So there's always 20 visible rows below my selected cell.

I won't be the first one to want this behaviour - does anyone know if it's possible, and if so how? Anything (pretty much) is possible in VBA, but I'd prefer a non-scripted solution, if possible - thanks!

Codemonkey
  • 335

2 Answers2

2

Right click on the sheet name and select view code, paste this code. Should be good to go

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.CountLarge > 1 Then Exit Sub
    Dim w As Window:    Set w = ActiveWindow
    Dim r As Range:     Set r = w.VisibleRange
    If Target.Row = r(1, 1).Row Then w.SmallScroll up:=1
    If Target.Row = r(1, 1).Offset(r.Rows.Count).Row - 20 Then w.SmallScroll down:=1
End Sub
Max
  • 21
1

It's quite understandable to someone who has dabbled in z/OS...

Outside of vba, I know of scroll lock which prevents the selection from changing whilst moving, and page up / down which keep the same visual cursor position, yet also move as many lines as are shown on screen; neither of which fit the bill.

I've designed a special case of this - it keeps the selection permanently centered, at least when the cell sizes are fixed (otherwise the computation's messy, as pointed out, and likely slower). The displayed range, ie screen size + zoom, can be determined dynamically at little extra cost. Also haven't handled range selection - will center according to the top left cell rather than center or ignore, nor adding the events dynamically to new sheets.

'Const SCRROWS = 24 ' Example screen size.
'Const SCRCOLS = 21
Global Pscrr As Long
Global Pscrc As Long

Function GetScreen() As Long()
Dim vret(2) As Long
If ActiveWindow.VisibleRange.Rows.Row + _
    ActiveWindow.VisibleRange.Rows.Count >= Rows.Count _
or ActiveWindow.VisibleRange.Columns.Column + _
    ActiveWindow.VisibleRange.Columns.Count >= Columns.Count Then
    vret(0) = Pscrr
    vret(1) = Pscrc
Else
    vret(0) = ActiveWindow.VisibleRange.Rows.Count
    vret(1) = ActiveWindow.VisibleRange.Columns.Count
    Pscrr = vret(0)
    Pscrc = vret(1)
End If
GetScreen = vret
End Function

Sub CenterScroll(slrow As Long, slcol As Long)
Dim nscrr As Long
Dim nscrc As Long
Dim scrsize() As Long
scrsize = GetScreen
nscrr = slrow - Int(scrsize(0) / 2)
If nscrr < 1 Then
    nscrr = 1
End If
nscrc = slcol - Int(scrsize(1) / 2)
If nscrc < 1 Then
    nscrc = 1
End If
ActiveWindow.ScrollRow = nscrr
ActiveWindow.ScrollColumn = nscrc
End Sub

Sub Button2_Click()
CenterScroll ActiveCell.Row, ActiveCell.Column
End Sub

'------Put this on the sheet which uses the scroll method------'
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
CenterScroll Target.Row, Target.Column
End Sub

Adding 20% - 80% edge limitation should be relatively straightforward (need to introduce a 'momentum' concept, then check whether selected row - visible top row > 0.8 * screen rows etc); however, a robust solution is beyond my scope.

SBM
  • 133