VBA Express : Excel - Pgdn Pgup from current to last sheet and back

Excel

Pgdn Pgup from current to last sheet and back

Ease of Use

Easy

Version tested with

2003

Submitted by:

grichey

Description:

This will allow you to switch back and forth between your current sheet and the last sheet. This is useful if you have a lot of sheets and an index or master at the end.

Discussion:

I use this code when I will work with one sheet then switch to the last sheet to update the master then switch back to the sheet I was working with, without having to shuffle through every sheet one by one. Ex. I am on sheet 3 of 7. I click ctrl alt pgdn to goto sheet 7 then ctrl alt pgup to go back to 3. If I am on sheet4 when I click the ctrl alt pg dn it will return back to sheet 4. thx to Charlize and xld

Code:

instructions for use

			 'in Workbook module
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
     'Restore the keys to their original state
    Application.OnKey "^%{PGUP}" 
    Application.OnKey "^%{PGDN}" 
End Sub 
Private Sub Workbook_Open() 
    Application.OnKey "^%{PGUP}", "FirstSheet" 'Ctrl-Alt-PageUp
    Application.OnKey "^%{PGDN}", "LastSheet" 'Ctrl-Alt-PageDown
End Sub 
 
 'in regular code module
Option Explicit 
Public csheet As Worksheet 
Sub FirstSheet() 'receive csheet
    If csheet Is Nothing Then 
        Set csheet = ActiveWorkbook.Worksheets(1) 
    End If 
    csheet.Select 
End Sub 
 
Sub LastSheet() 
    Set csheet = ActiveWorkbook.ActiveSheet 
     'return csheet to top
    ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count).Select 
End Sub

How to use:

Copy and paste top section into workbook module. Copy and paste bottom section into regular code module. Save and exit. On opening the workbook, ctrl alt pgdn/up will work as described.

Test the code:

Create a workbook and add code as described then save and exit and reopen.
Press ctrl+alt+pgdn - active sheet should switch to your last sheet.
Press ctrl+alt+pgup - active sheet should now be the one you were previously using

Sample File:

PgUp_PgDn_Jump.zip 9.1KB

Approved by mdmackillop

This entry has been viewed 228 times.