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

 

[Stringt]

yeah it does if you have the sheets next to each other. but if the workbook has a lot of sheets and you don't want to move them around then a shortcut to flip between two would b useful... guess I am just lazy though!

 

[dmckinney]

looking more closely at my 2nd solution....
probably isn't very useful, as if you've got more than one sheet selected, and enter a value in a cell, the value gets entered in the same cell on the other sheets.

The solution will be along the same lines.

if you're a coder then the following ideas might help....
...a userform modeless userform using withevents to respond to the change events of the active workbook, and store in a form level variable (or private property) the name of the last worksheet.

However again I may be complicating things.

dave.
This message was edited by dmckinney on 2002-10-07 05:18

 

[dmckinney]

OK...so here's the final post from me...got to do some work!

1) Create a small userform with a button on it. Call the button cmdSwitch.
2) **Make sure the showmodal property is set to false.**
3) Add the code below to the form.
4) Make a button (or shortcut) to load the form


The code works with the active workbook so isn't tied to a particular work book.

TO USE
******
Once the form is loaded click on a second sheet, then use the button to toggle between sheets.

Private WithEvents mywkb As Excel.Workbook
Private mstrSheetToSwitchTo As String

Private Sub cmdSwitch_Click()
    mywkb.Sheets(mstrSheetToSwitchTo).Activate
End Sub

Private Sub mywkb_SheetDeactivate(ByVal Sh As Object)
    mstrSheetToSwitchTo = Sh.Name
End Sub

Private Sub UserForm_Initialize()
Set mywkb = ActiveWorkbook
    mstrSheetToSwitchTo = mywkb.ActiveSheet.Name
End Sub

Private Sub UserForm_Terminate()
    Set mywkb = Nothing
End Sub

 

[Stringt]

thanks... i have almot got there but how do I do step 4?

 

[Stringt]

ignore that - done it now... thanks a lot. pretty smart

 

[rishima]

In the bottom left where the arrow keys are to scroll between tabs, simply right-click and it will bring up a list of tabs to go to...

 

[Thanksamillion]

I've got a code that works for switching between active and last worksheets. This code uses the sheetdeactiveate event and must be in the ThisWorkbook object. I saved it as a excel addin. The macro uses [Alt]+[Caps Lock] for activating the code.

Problem - I can't get the addin sheet ThisWorkbook object to be recognized on any other workbook. This is causing the Macro to fail, or more accuratley, to not run at all. If someone could solve this issue I would really appreciate it.

Thanks

Public Lsheet As Worksheet
Public NSheet As Worksheet
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
On Error Resume Next
Set Lsheet = NSheet
Set NSheet = ActiveSheet
End Sub
Sub onkey()
Application.onkey "%{CAPSLOCK}", "LastSheet"
End Sub
Sub LastSheet()
Lsheet.Activate
End Sub

 

[shg]

Welcome to the board.

Try this instead:

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}", "LastSheet"
End Sub
 
Private Sub app_SheetDeactivate(ByVal Sh As Object)
    Set wksOld = wksNew
    Set wksNew = ActiveSheet
End Sub
 
Sub LastSheet()
    If Not wksOld Is Nothing Then wksOld.Select
End Sub

 

[Thanksamillion]

First thanks for your quick reply. However this set of code is having the same problem as the one I included. The sheetdeactivate event is not running when I place it in the add-in. The code is in the ThisWorkbook object of the add-in workbook. I am trying to write the macro so that it will be available on any workbook and activate when switching between any two sheets on any two open workbooks. I am not sure that this is entirly possible but if it is I would apprecidate the help. thanks

 

[shg]

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