Very useful code, if someone can help me design it.

[mikechambers]

I want to design something in Excel that would allow me to switch back and forth between tabs, similar to how you can switch back and forth between applications using Alt-Tab. What I can't figure out is how to have the code write the selected sheet name to a variable as soon as the tab is selected. So I think you would have two variables. When you click on a different tab, it writes the sheet name to var1. When you click on another tab, it writes to var2. When you press the hot key to run the code, it goes back to var 1. And the hot key would just keep going back and forth between the two most recent tabs. Think it's possible?

 

[rajibdas]

Hi... To move between tabs - I'd prefer to use Crtl+PgUp or Crtl+PgDn.
If it helps.

Regards

 

[mikechambers]

well, yeah, but if the two tabs are separated by a bunch of tabs, it is a huge pain.

 

[sweater_vests_rock]

mike.

the built-in excel shortcut for navigating between tabs is

Move to the next sheet in the workbook.
CTRL+PAGE DOWN

Move to the previous sheet in the workbook.
CTRL+PAGE UP

for more shortcuts, check out the microsoft site.
cheers. ben.

[edit: didn't see previous posts until too late. sorry for redundancy.]

 

[mikechambers]

Okay, already know about this shortcut. Thanks. I am looking for something similar to the Last Channel or Flashback button on your TV remote. Something to help when the tabs are separated by 20 other tabs, PLUS so you can press the same hot key to go back and forth. Even if the two tabs were next to each other, you have use two different hot keys, on to move right, and a different on to move left.

 

[sweater_vests_rock]

mike.

copy this into the thisworkbook module:

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Set Module1.shtPrevious = Sh
End Sub

copy this into a standard module (named module1):

Public shtPrevious As Object

Sub GotoPreviousSheet()
    shtPrevious.Activate
End Sub

assign a shortcut key and you should be good to go.

Cheers. Ben.