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

mikechambers

Active Member
Joined
Apr 27, 2006
Messages
397
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?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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.]
 
Upvote 0
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.
 
Upvote 0
mike.

copy this into the thisworkbook module:
Code:
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Set Module1.shtPrevious = Sh
End Sub

copy this into a standard module (named module1):
Code:
Public shtPrevious As Object

Sub GotoPreviousSheet()
    shtPrevious.Activate
End Sub

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

Cheers. Ben.
 
Upvote 0

Forum statistics

Threads
1,225,073
Messages
6,182,709
Members
453,132
Latest member
nsnodgrass73

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top