I'm trying to use VBA to create a "Back" button that allows me to go back through, in order, a series or previously active worksheets.
I have a an Excel (365) workbook with over 200 worksheets and I use a "Navigation Bar" (i.e. Group of simple "Goto" macros) at the top of each worksheet to navigate between 6 different "Modules" within the same workbook. The Navigation Bar allows me to navigate to "Secondary" sub-menus within in each of the "modules" using other "Goto" macros. From there I can drill down to more levels within each module, ultimately drilling down up to 5 levels.
The code below works if I continue to "drill-down" within the same module or move between modules. It allows me to return all the way back to Level 1 (i.e. The MainPage), however, something goes wrong when I try to:
In VBA Module
In ThisWorkbook
I have a an Excel (365) workbook with over 200 worksheets and I use a "Navigation Bar" (i.e. Group of simple "Goto" macros) at the top of each worksheet to navigate between 6 different "Modules" within the same workbook. The Navigation Bar allows me to navigate to "Secondary" sub-menus within in each of the "modules" using other "Goto" macros. From there I can drill down to more levels within each module, ultimately drilling down up to 5 levels.
The code below works if I continue to "drill-down" within the same module or move between modules. It allows me to return all the way back to Level 1 (i.e. The MainPage), however, something goes wrong when I try to:
- Move "up and down" within the same module (i.e. If I'm at a 3rd Level menu and select an option to take me to the 4th Level and use the "Back" Button to bring me back to the 3rd Level and then select a different path to get to the 4th Level, when I use the "Back" button again, instead of bringing me back to the 3rd Level, it brings me back to the 2nd Level.
In VBA Module
VBA Code:
Option Explicit
Dim History As New Collection
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim wksht As Worksheet
If History.Count > 0 Then
If Sh.Name = History(History.Count).Name Then Exit Sub
End If
Set wksht = Sh
History.Add wksht
If History.Count > 20 Then History.Remove 1
End Sub
Sub Go_Back_In_History()
If History(History.Count).CodeName = ActiveSheet.CodeName Then History.Remove History.Count
Application.EnableEvents = False
On Error Resume Next
History(History.Count).Activate
On Error GoTo 0
Application.EnableEvents = True
History.Remove History.Count
End Sub
In ThisWorkbook
VBA Code:
Global LastSheet As String
Sub GoBack()
Call ThisWorkbook.Go_Back_In_History
End Sub
Last edited by a moderator: