Controlling User Scrolling
An Excel worksheet contains more than 4 millions cells (16 million in Excel 97). Most of the worksheets that you develop use only a tiny portion of the available area in a worksheet. Novice users sometimes get "lost" 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