One of the features that consumers may find useful is retracing their path as they navigate through different workbooks, different sheets, and different windows. This navigation would be equivalent of a web browser’s Previous and Next feature. While Excel does not support this natively, the code in this note enables this capability.
The zip file contains the Excel workbook with macros (XLSM extension).
Download the zip file and save the extracted workbook to a convenient folder. Alternatively, create a new workbook, add the code from the next section, and save the workbook to a convenient folder. Then, whenever this saved workbook is open, the Navigation Retrace capability will be active.
Alternatively, save the workbook as an add-in. Then, loading the add-in will enable the capability.
Once the navigation system is enabled, use ALT + ← (i.e., left-arrow) key to go to the previous sheet / window visited and ALT + → (i.e, right-arrow) key to move forward. Note that this is defined in terms of how the actual navigation occurs and not how the workbooks, sheets, and windows are organized.
For example, suppose a workbook has 5 sheets, Sh1, Sh2, Sh3, Sh4, and Sh5. Suppose Sh1 is visible when the workbook is opened. Then, suppose the consumer clicks on Sh3 and then on Sh5. Now, ALT + ← will go to Sh3, and a repeat operation will go to Sh1, i.e., retracing the consumer’s path.
Of course, after reaching Sh1 no further ‘Previous’ step is possible. But, using ALT + → will do the equivalent of ‘next’ and move to Sh3 and then to Sh5. Any action that affects the active sheet / window resets the “forward” path to the now active sheet / window. So, assume that, in the above example, the consumer was on Sh1 after stepping back twice. Now, clicking on Sh2 (or opening a new workbook) will remove Sh3 and Sh5 from the navigation path and, instead, insert Sh2 (or the new workbook’s active sheet) into it.
The code goes into three different modules. Note that the code should be in a workbook by itself. It should not be duplicated in multiple workbooks. Then, opening the workbook containing the code will enable the navigation capability. Alternatively, save the file as an add-in. Then, whenever loading the add-in will enable the capability.
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
stopSys
End Sub
Private Sub Workbook_Open()
Application.OnTime Now(), "DelayedStart"
End Sub
Option Explicit
Public Const NbrElements As Integer = 10
'How far back one can go
Const NextKey As String = "%{RIGHT}", _
PrevKey As String = "%{LEFT}"
'ALT+right key for next _
ALT+left key for previous. _
To use other key combinations see _
Excel VBA help for the OnKey method
Dim AppClass As clsApp
Sub goNext()
AppClass.goNext
End Sub
Sub goPrev()
AppClass.goPrev
End Sub
Sub delayedStart()
Set AppClass = New clsApp
Application.OnKey NextKey, "goNext"
Application.OnKey PrevKey, "goPrev"
If Not ActiveSheet Is Nothing Then _
AppClass.addObj ActiveSheet
End Sub
Sub stopSys()
Application.OnKey NextKey
Application.OnKey PrevKey
Set AppClass = Nothing
End Sub
Option Explicit
Dim WithEvents App As Application
Dim ObjList(NbrElements - 1) As Object
Dim Curr As Integer, First As Integer, Last As Integer
Private Sub addOne(ByRef Ptr As Integer)
If Ptr = UBound(ObjList) Then Ptr = LBound(ObjList) _
Else Ptr = Ptr + 1
End Sub
Private Sub minusOne(ByRef Ptr As Integer)
If Ptr = LBound(ObjList) Then Ptr = UBound(ObjList) _
Else Ptr = Ptr - 1
End Sub
Public Sub addObj(Obj As Object)
addOne Curr
Set ObjList(Curr) = Obj
Last = Curr
If First = -1 Then First = Curr _
Else If First = Curr Then addOne First
End Sub
Private Sub App_NewWorkbook(ByVal Wb As Workbook)
Debug.Print "NewWorkbook: " & Wb.Name
End Sub
Private Sub App_SheetActivate(ByVal Sh As Object)
Debug.Print "SheetActivate: " & Sh.Name
addObj Sh
End Sub
Private Sub App_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
Debug.Print "WindowActivate: " & Wb.Name & ": " & Wn.Caption
addObj Wn
End Sub
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
Debug.Print "WorkbookOpen: " & Wb.Name
End Sub
Public Sub goNext()
If Curr = Last Then Beep: Exit Sub
addOne Curr
Application.EnableEvents = False
On Error Resume Next
ObjList(Curr).Activate
Application.EnableEvents = True
End Sub
Public Sub goPrev()
If Curr = First Then Beep: Exit Sub
minusOne Curr
Application.EnableEvents = False
On Error Resume Next
ObjList(Curr).Activate
Application.EnableEvents = True
End Sub
Private Sub Class_Initialize()
Set App = Application
'ReDim PrevObjList(NbrElements - 1)
Curr = -1: Last = -1: First = -1
End Sub
Private Sub Class_Terminate()
Dim I As Integer
On Error Resume Next
For I = LBound(ObjList) To UBound(ObjList)
Set ObjList(I) = Nothing
Next I
On Error GoTo 0
End Sub
1) Because this is a “code only” solution it lacks a “proper” UI. This has implications.
a. There’s no elegant way to turn on and turn off the capability. Opening the workbook containing the code enables the capability. Closing the workbook disables it.
b. The only way to use the capability is shortcut keys. By default ALT + ← is ‘previous’ and ALT + → is ‘next.’ To change this, modify the appropriate constants in the main code module.
c. The default number of objects tracked in the navigation path is 10. To increase (or decrease) this number, modify the appropriate constant in the main code module.
2) The code does not contain sophisticated handling of objects that, after insertion in the navigation path, no longer exist. This can happen if someone closes a workbook or a window or deletes a sheet. Also no advanced handling exists for ‘immediate duplicates,’ something that can happen when one closes a workbook.
Suppose workbook WB1 Sheet1 is active. Then, one moves to WB2 Sheet2 and closes WB2. Now, Excel will automatically show WB1 Sheet1.
The Navigation Path will contain WB1 Sheet1, WB2 Sheet2, WB1 Sheet1. Given that WB2 is closed, the path should be ‘cleaned’ to remove WB2 Sheet2 and then consolidate the duplicate WB1 Sheet1 entries that are “next” to each other. The current code does not implement this level of logic.