VBA ALT TAB equivalent within workbook

[Catius]

Hi everyone,
I use the following code (found there) to switch back and forth between the last worksheet showed and the current one (so like alt-tab but between the sheets as opposed to between workbooks). I was wondering how the code could be tweaked so that it switches between sheets within the same workbook when you have multiple workbooks open. Eg you have 2 workbooks: workbook 1 with sheets 1, 2; workbook 2 with sheets 1, 2. When going from workbook 1 sheet 1, then sheet 2 to workbook 2 sheet 1 then back to workbook 1 sheet 1 pressing the macro would take me to workbook 1 sheet 2 (and not workbook 2 sheet 2). Any idea how to do the tweak please? Many thanks!

Dim TabTracker As New TabBack_Class

Sub TabBack_Run()
'PURPOSE: Initiate Tab tracking and shortcut key trigger
'SOURCE: www.TheSpreadsheetGuru.com

'Enable TabTracker class
Set TabTracker.AppEvent = Application

'Call ToggleBack macro when user keys alt + `
Application.OnKey "%`", "ToggleBack"

End Sub

Sub ToggleBack()
'PURPOSE: Go Back to Previous Worksheet
'SOURCE: www.TheSpreadsheetGuru.com

With TabTracker
On Error Resume Next
Workbooks(.WorkbookReference).Worksheets(.SheetReference).Activate
On Error GoTo 0
End With

End Sub

Public WithEvents AppEvent As Application
Public SheetReference As String
Public WorkbookReference As String

Private Sub AppEvent_SheetDeactivate(ByVal Sh As Object)
'PURPOSE: Store active worksheet information before leaving it
'SOURCE: www.TheSpreadsheetGuru.com

  WorkbookReference = Sh.Parent.Name
  SheetReference = Sh.Name
  
End Sub

Private Sub AppEvent_WorkbookDeactivate(ByVal Wb As Workbook)
'PURPOSE: Store active worksheet information before closing workbook
'SOURCE: www.TheSpreadsheetGuru.com

  WorkbookReference = Wb.Name
  SheetReference = Wb.ActiveSheet.Name

 

[Zot]

This Class module modification seems to work. It will cycle through every sheet in a workbook before moving to another workbook even if the number of sheet not the same. I tried 3rd workbook also seems to work

Public WithEvents AppEvent As Application
Public SheetReference As String
Public WorkbookReference As String
Public WBIndex As Long, ShtIndex As Long

Private Sub AppEvent_SheetDeactivate(ByVal Sh As Object)
'PURPOSE: Store active worksheet information before leaving it
'SOURCE: www.TheSpreadsheetGuru.com

  WorkbookReference = Sh.Parent.Name
  ShtIndex = ActiveSheet.Index
  
  If Not ShtIndex = Sheets.Count Then
        ShtIndex = ShtIndex + 1
        SheetReference = Sheets(ShtIndex).Name
    Else
        If Not WBIndex = Workbooks.Count Then
            WBIndex = WBIndex + 1
        Else
            WBIndex = 1
        End If
        WorkbookReference = Workbooks(WBIndex).Name
        SheetReference = Sheets(1).Name
    End If
  
End Sub

Private Sub AppEvent_WorkbookDeactivate(ByVal wb As Workbook)
'PURPOSE: Store active worksheet information before closing workbook
'SOURCE: www.TheSpreadsheetGuru.com

  SheetReference = wb.ActiveSheet.Name
  WBIndex = Index(wb)
  
    If Not WBIndex = Workbooks.Count Then
        WBIndex = WBIndex + 1
    Else
        If Not ShtIndex = wb.Sheets.Count Then
            ShtIndex = ShtIndex + 1
        Else
            WBIndex = 1
        End If
        WorkbookReference = Workbooks(WBIndex).Name
        SheetReference = wb.Sheets(ShtIndex).Name
    End If
  
End Sub

Private Function Index(wb As Workbook) As Integer

Dim i As Integer

For i = 1 To Workbooks.Count
    If Workbooks(i).Name = wb.Name Then
        Index = i
        Exit For
    End If
Next i

End Function

 

[Catius]

Thank you so much Zot! It works almost perfectly. Only thing is when I close a workbook, I get the following error. Should we throw an "on error resume next", or maybe a closeworkbook event to handle the index or what would you recommend?

 

[Zot]

Subscript out of range because the sheet cannot be found anymore since you close the workbook most likely.

Yes, just throw On Error Resume Next will not cause any problem since you are closing the workbook anyway. I don't remember seeing any error. ?

 

[Catius]

Thank for your answer. Actually upon investigating a bit more, first, I get another error in the sub "AppEvent_SheetDeactivate" at the line "WorkbookReference = Workbooks(WBIndex).Name" (after I close a workbook and trigger the key). Second, it seems the macro doesn't behave exactly in the way I thought (weird I didn't get that when testing it before): it seems to circulate across sheets (and workbooks), whereas I thought of trying to make it a bit more like an ALT+TAB within a workbook (ie when triggered go to last seen sheet within the workbook).

I have tried the below, taking inspiration from how you cycle through your sheets but while it works within a sheet, it "forgets" the parameters when moving from one to the next workbook:

Public WithEvents AppEvent As Application
Public SheetReference, SheetReference1, SheetReference2 As String
Public WorkbookReference, WorkbookReference1, WorkbookReference2 As String
Public WBIndex As Long

Private Sub AppEvent_SheetDeactivate(ByVal Sh As Object)
'PURPOSE: Store active worksheet information before leaving it
'SOURCE: www.TheSpreadsheetGuru.com

If Sh.Parent.Name <> WorkbookReference1 Then
  WorkbookReference = Sh.Parent.Name
  WorkbookReference2 = WorkbookReference
  SheetReference = Sh.Name
  SheetReference2 = SheetReference
Else
  WorkbookReference = Sh.Parent.Name
  WorkbookReference1 = WorkbookReference
  SheetReference = Sh.Name
  SheetReference1 = SheetReference
End If
  
End Sub

Private Sub AppEvent_WorkbookActivate(ByVal wb As Workbook)
'PURPOSE: Store active worksheet information before closing workbook
'SOURCE: www.TheSpreadsheetGuru.com

    If wb.Name <> WorkbookReference Then
        'we moved to a new WB, we save previous info in 1
        WorkbookReference1 = WorkbookReference
        WorkbookReference2 = wb.Name
        WorkbookReference = wb.Name
        SheetReference1 = SheetReference
        SheetReference = wb.ActiveSheet.Name
        SheetReference2 = SheetReference
    Else
        'we stayed in the same workbook
        SheetReference = wb.ActiveSheet.Name
        SheetReference1 = SheetReference
    End If
    
    
End Sub

Dim TabTracker As New TabBack_Class

Sub TabBack_Run()
'PURPOSE: Initiate Tab tracking and shortcut key trigger
'SOURCE: www.TheSpreadsheetGuru.com

'Enable TabTracker class
  Set TabTracker.AppEvent = Application
  
'Call ToggleBack macro when user keys alt + `
  Application.OnKey "%²", "ToggleBack"

End Sub

Sub ToggleBack()
'PURPOSE: Go Back to Previous Worksheet
'SOURCE: www.TheSpreadsheetGuru.com
With TabTracker
On Error Resume Next
  If .WorkbookReference1 = ActiveSheet.Parent.Name Then
    Workbooks(.WorkbookReference1).Worksheets(.SheetReference1).Activate
  On Error GoTo 0
    Else
    Workbooks(.WorkbookReference2).Worksheets(.SheetReference2).Activate
  End If
End With


End Sub

 

[Zot]

The behavior I tested it if say wb1 has 2 sheets and wb2 has 3 sheets, the cycle was
wb1 sht1 > wb1 sht 2 > wb2 sht1 > wb2 sht2 > wb2 sht3 > wb1 sht1 > wb1 sht 2 > and so on.

When closing one wb, then it will cause error because the macro is looking for the closed workbook and the object was not found. That can be solved by adding On Error Resume Next I believe.

I found out that when I opened up another wb, it will not be recognized until I manually click a sheet on any workbook I think (I cannot recall).

 

[Catius]

Thanks Zot. I see; is there a way not to cycle through but do more like a alt tab (as I unsuccessfully tried)?

 

[Zot]

Thanks Zot. I see; is there a way not to cycle through but do more like a alt tab (as I unsuccessfully tried)?

I thought that was what you've wanted

The original one with wb1 (2 sheets) and wb2 (2 sheets), it toggles from wb1 sht1 > wb2 sht2 > wb1 sht1 > wb2 sht2 > and so on right? I don't understand what you meant by Alt Tab behavior. I thought the original is like the Alt Tab

 

[Catius]

Thanks Zot and sorry for the confusion! Meanwhile I actually have coded and tried the below which seems to work. By ALT TAB I mean the shortcut bringing you to the last seen sheet

Sub ToggleBack()
'PURPOSE: Go Back to Previous Worksheet
'SOURCE: www.TheSpreadsheetGuru.com

With TabTracker
'On Error Resume Next
  If .WorkbookReference1 = ActiveSheet.Parent.Name Then
    Workbooks(.WorkbookReference1).Worksheets(.SheetReference1).Activate
  'On Error GoTo 0
    Else
    Workbooks(.WorkbookReference2).Worksheets(.SheetReference2).Activate
  End If
End With


End Sub

Public WithEvents AppEvent As Application
Public SheetReference, SheetReference1, SheetReference2 As String
Public WorkbookReference, WorkbookReference1, WorkbookReference2 As String
Public WBIndex As Long

Private Sub AppEvent_SheetDeactivate(ByVal Sh As Object)
'PURPOSE: Store active worksheet information before leaving it
'SOURCE: www.TheSpreadsheetGuru.com

If Sh.Parent.Name <> WorkbookReference1 Then
  'moved to new workbook
  're-initilize in case it's neither 1 nor 2
  If WorkbookReference2 = vbNullString Then WorkbookReference2 = Sh.Parent.Name
  If Sh.Parent.Name <> WorkbookReference2 Then
    WorkbookReference1 = Sh.Parent.Name
    SheetReference1 = Sh.Name
  Else
    SheetReference2 = Sh.Name
  End If
Else
  SheetReference1 = Sh.Name
End If
  
End Sub

Private Sub AppEvent_WorkbookActivate(ByVal wb As Workbook)
'PURPOSE: Store active worksheet information before closing workbook
'SOURCE: www.TheSpreadsheetGuru.com

    If wb.Name <> WorkbookReference1 Then
        'we moved to a new WB1
        If WorkbookReference2 = vbNullString Then WorkbookReference2 = wb.Name
        're-initilize in case it's neither 1 nor 2
        If wb.Name <> WorkbookReference2 Then
            WorkbookReference1 = wb.Name
            SheetReference1 = wb.ActiveSheet.Name
        Else
         '   SheetReference2 = wb.ActiveSheet.Name
        End If
    Else
        'we stayed in the same workbook
       ' SheetReference1 = wb.ActiveSheet.Name
    End If
    
    
End Sub


Private Sub AppEvent_Workbook_Open(ByVal wb As Workbook)
'initialize
WorkbookReference1 = wb.Name
    
End Sub