Switching between sheets - shortcut?

[Stringt]

I often find myself moving from one sheet in a workbook to another over and over. I wanted a shortcut that moved between the last two sheets selected.

Similar to how Alt+Tab works with windows.

Does anybody know a keyboard shortcut or if not is there a macro that could be added to perosnal.xls that would mean i would aways b able to switch between two sheets quickly.

Cheers

Tim

 

[Thanksamillion]

Hey shg thanks for the brilliant bit of code, it worked great! I really appreciate it. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[shg]

You're welcome, good luck.

 

[sangram.takmoge]

I often find myself moving from one sheet in a workbook to another over and over. I wanted a shortcut that moved between the last two sheets selected.

Use Ctrl + Page up /Page Down.

 

[JLGWhiz]

Maybe I'm missing the point of all this, but isn't using the sheet name tabs within the same workbook, or using the file name tabs on the task bar for different workbooks just as fast as using a keyboard shortcut?

 

[guts80]

Re: Updating code to work with chartsheet

Shg, the code works great for worksheets...but fails for chartsheets. Since there is no sheet object (to reference either worksheet OR a chart sheet) where should the conditional decalaration of the variable go to make this code work for both types of sheets?

BTW, i'm using this code within a personal workbook (code bplaced in thisworkbook of personal workbook) instead of an add-on.

Cheers

 

[guts80]

Re: Updating code to work with chartsheet

Shg, the code works great for worksheets...but fails for chartsheets. Since there is no sheet object (to reference either worksheet OR a chart sheet) where should the conditional decalaration of the variable go to make this code work for both types of sheets?

BTW, i'm using this code within a personal workbook (code bplaced in thisworkbook of personal workbook) instead of an add-on.

Cheers

Whoops just figured out how to solve this problem: Just change the variables to "Object" instead of "Worksheet". The code will then work for both chartsheet and worksheet.

Cheers!

 

[rhincodon]

Hey shg thanks for the brilliant bit of code, it worked great! I really appreciate it. <!--?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /--><o></o>

Thank you all, it worked beautifully on my Excel 2007.

And for those who are still confused of this macro versus ctrl+pg up/down, the former will allow you to switch between non-adjacent tabs (eg. sheet 1 and sheet 5) while the latter is for switching between adjacent tabs only (sheet 1 and 2).

 

[shimon.amar]

Ah, the benefits of testing ....

Option Explicit
 
Public WithEvents app As Application
Dim wksOld          As Worksheet
Dim wksNew          As Worksheet
 
Private Sub Workbook_Open()
    Set app = Application
    Application.OnKey "%{CAPSLOCK}", ThisWorkbook.Name & "!Thisworkbook.LastSheet"
End Sub
 
Private Sub app_SheetActivate(ByVal Sh As Object)
    Set wksOld = wksNew
    Set wksNew = Sh
End Sub
 
Sub LastSheet()
    If Not wksOld Is Nothing Then wksOld.Select
End Sub

Can you please tell me (step by step) how and where should I implanting this kind of code? very frustrated of the lack of knowledge cause, if I understood right, this is not a regular macro to put on a module...