Controlling User Scrolling

Category: General VBA | [Item URL]

An Excel worksheet contains millions of cells. Most of the worksheets that you develop use only a tiny portion of the available area in a worksheet. Novice users sometimes get los" in a worksheet. For example, they may hit PgDn a few times and be faced with an apparently blank worksheet.

Hiding Rows and Columns

You can prevent users from scrolling around unused areas of a worksheet by hiding the unused rows and columns using the Format Column Hide and Format Row Hide commands. For example, if the active area in your worksheet consists of the range A1:G25, you can hide columns H through IV and rows 26 through 16384. The result is an apparently smaller workbook that doesn't display any unused rows or columns.

After hiding unused rows, you may discover a problem: If the user presses the PgDn key when the active cell is near the last unhidden row, the worksheet scrolls up and displays a blank area -- in fact, the entire worksheet may scroll out of view. Obviously, this can cause even more confusion than displaying empty rows.

Trapping the PgDn Key

The solution to the problem described above is to trap the PgDn key. In VBA, you can use Excel's OnKey method to execute a subroutine whenever a particular keystroke occurs. The subroutine below, (which is executed when the workbook is opened), causes a subroutine named DownOne to be executed whenever the user presses the PgDn key.

Sub Auto_Open()
    Application.OnKey "{PgDn}", "DownOne"
End Sub

You'll also need an Auto_Close subroutine to restore the key to normal when the workbook is closed.

Sub Auto_Close()
    Application.OnKey "{PgDn}"
End Sub

The DownOne subroutine, listed below, moves the active cell to the row below -- but only if the row is not hidden. The net effect is that pressing PgDn mimics the down arrow key.

Sub DownOne()
    If Not ActiveCell.Offset(1, 0).EntireRow.Hidden Then _
        ActiveCell.Offset(1, 0).Activate
End Sub

Fine-Tuning

The procedures listed above cause the DownOne subroutine to be executed whenever PgDn is pressed. However, you may want this subroutine to be executed only when the workbook with the hidden rows and columns is activated. In other words, you may want PgDn to operate normally in all workbooks except the one that contains the hidden rows and columns.

The procedures listed below accomplish this. Whenever a sheet is activated, the TrapKey subroutine is executed. The TrapKey subroutine sets up the OnKey event if the active workbook is the workbook that contains the hidden rows and columns; otherwise, the OnKey event is cancelled.

Sub Auto_Open()
    Application.OnSheetActivate = "TrapKey"
End Sub

Sub Auto_Close()
    Application.OnSheetActivate = ""
End Sub

Sub TrapKey()
    If ActiveWorkbook Is ThisWorkbook Then _
        Application.OnKey "{PgDn}", "DownOne" Else _
            Application.OnKey "{PgDn}"

End Sub

Sub DownOne()
    If Not ActiveCell.Offset(1, 0).EntireRow.Hidden Then _
        ActiveCell.Offset(1, 0).Activate
End Sub