Home > Publications & Training > Case Studies > TM Excel Dynamic Path Navigator
Google
Web This Site

Excel Dynamic Path Navigation

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

How to Use

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

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.

The ThisWorkbook module

 

Option Explicit

 

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    stopSys

    End Sub

 

Private Sub Workbook_Open()

    Application.OnTime Now(), "DelayedStart"

    End Sub

 

A standard code module

 

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

 

The clsApp class module

 

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

 

Caveats and limitations

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.

 

Comments