Solved: Jump to last worksheet

[grichey]

We all know ctrl + pg dn / pg up cyles up and down through sheets. Is there anyway to assign a short cut like ctrl alt a or something to jump to between the last and current work sheets? The reason for wanting to do this would be to skip and the ctrl dn'ing and up'ing and not being able to simply move the sheets closer together.

[Bob Phillips]

[vba]

Public Sub Test()

Application.OnKey "^%{PGUP}", "FirstSheet" 'Ctrl-Alt-PageUp
Application.OnKey "^%{PGDN}", "LastSheet" 'Ctrl-Alt-PageDown
End Sub


Sub FirstSheet()
ActiveWorkbook.Worksheets(1).Select
End Sub

Sub LastSheet()
ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count).Select
End Sub
[/vba]

[grichey]

Where might one put this code? Is ^ ctrl and % alt?

[Bob Phillips]

Wherever one wants!

Yes it is.

[grichey]

I tried putting this into both the workbook module and a regular module but hitting ctrl alt pgdn or up doesn't appear to do anything.

[Bob Phillips]

The procedure Test just primes those keys so you have to run that procedure.

[grichey]

ah, Is there no way to enable it without running the procedure? I'm just wondering if there's a way to make it easier for the users w/out having them alt+f8 everytime they open the file.

[Bob Phillips]

Change the application configuration without running any code? Now that is an interesting concept ...

Put it in the Workbook_Open event.

[grichey]

duh is probably appropriate.

[grichey]

How would I come up with the subscript for the current sheet?

like if I wanted to switch between current and end instead of end and first then back? Where I would just temporarily store the current sheet# as a variable?

[grichey]

Something like this only that works.
[VBA]Private Sub Workbook_Open()

Application.OnKey "^%{PGUP}", "FirstSheet" 'Ctrl-Alt-PageUp
Application.OnKey "^%{PGDN}", "LastSheet" 'Ctrl-Alt-PageDown
End Sub


Sub FirstSheet()

ActiveWorkbook.csheet.Select
End Sub

Sub LastSheet()
Dim csheet As Worksheet
csheet = ActiveWorkSheet
ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.count).Select
End Sub
[/VBA]