VBA Back Button to navigate back through a series of previously visited worksheets

[Lexcon07]

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:

• 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.

Any advice or guidance you can provide would be greatly appreciated. Thank you in advance!

In VBA Module

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

Global LastSheet As String

Sub GoBack()
    Call ThisWorkbook.Go_Back_In_History
End Sub

 

[Dan_W]

I have to be honest - I'm really intrigued by the idea of it, but I'm struggling to visualise how this works or functions. Admittedly, I've never this kind of need, so maybe this is the better solution. For me, I think if I needed a way of navigating a large number of sheets, I'd approach it graphically. My first suggestion would be to use the Worksheet Activation window - on the offchance you don't know about it, it's a popup window of all the worksheets in a workbook set out in a listbox. It can be access by right clicking on the two arrows that sit about the "Ready" notice in the bottom left-hand of the Excel window (please see attached picture).

The problem with that approach, though, is that it doesn't meet this 'go back in history' function you have. In that case, I think I'd still need a visual way of navigating through it all, so would probably make a userform, which a listbox that listed all the past worksheets I visited...

 

[GWteB]

Hi Lexcon07, welcome to MrExcel!

Try this. It goes in the ThisWorkbook module

Option Explicit

Private History As Collection

Private Sub Workbook_Open()
    Set History = New Collection
    History.Add ActiveSheet
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    History.Add Sh
    If History.Count > 20 Then
        History.Remove 1
    End If
End Sub


Public Sub Go_Back_In_History()
    Dim lLast   As Long
    lLast = History.Count
    If Not lLast <= 1 Then
            Application.EnableEvents = False
            History(lLast - 1).Activate
            Application.EnableEvents = True
            History.Remove lLast
    End If
End Sub

 

[Lexcon07]

Hi

Sorry, I figured it out and forgot to check back.

It's a really useful feature, so if anyone is interested in seeing how it works, I;d be happy to share.

Thanks anyway!

 

[Yongle]

It's a really useful feature, so if anyone is interested in seeing how it works, I;d be happy to share.

why care if anyone is interested today?
- please share the solution regardless
- give future readers the benefit of your solution ✔

 

[Lexcon07]

I use a lot of simple "Goto" macros to create a User Interface (Navigation Bar and multiple Menu Pages/Sub-Pages) to allow users to navigate throughout my models, drill-down from Executive Level Summaries to source data and get to where they want to without ever having to look for the right worksheet (Most of my models have over 200 worksheets). I also include integrated User Guides, with "Goto" macros to allow them to go from the User Guide to the specific part of the model the User Guide is speaking to and right back to the User Guide, and Assumption Logs that link each Assumption with the specific areas in the model where they are relevant.

Needless to say, there is a lot of "jumping" around the model and instead of forcing the Users to go back to the Main Page every time and start down a slightly different path, the following code allows users to trace back their steps based on the worksheets they last visited. They can back track to the next sub-page and go down a different path or jump back and forth between the User Guide and the model or the Assumption Log and the model. To me this is an essential feature of any robust or remotely complicated Excel Model. For me, a slick User Interface really differentiates a model from a spreadsheet.

The "Back" button is integrated with my Navigation Bar which I include at the top of every page in my model.

In VBA Module

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
End Sub

In ThisWorkbook

Global LastSheet As String

Sub GoBack()
Call ThisWorkbook.Go_Back_In_History
End Sub

Hope someone finds this useful for the too!

Thanks for reading.