VBA Express : Excel - Navigate Back to Previous Locations in a Workbook.

Excel

Navigate Back to Previous Locations in a Workbook.

Ease of Use

Intermediate

Version tested with

2000

Submitted by:

johnske

Description:

The function GoToRange memorizes the address of the top left cell of the active worksheet in an array, then goes forward to the (new) location specified. The procedure GoBack goes back to the previous location and then removes the address of the location that it's just left from the array. (GoToRange is not restricted to a given number of addresses - there can be as many as you wish)

Discussion:

You may have a text box or embedded word document with a lengthy text. To keep the flow of the document going you may decide to extract things like charts, formulas, explanations, data etc. from it and place these somewhere else as optional reading so as to make the main body of text shorter and more readable with (maybe) navigation buttons placed in the text. But after reading the optional material (whatever the choice and wherever it may be) you want the reader to navigate straight back to their previous location and view it exactly as they left it i.e. with the same cell (whatever it may be) in the top-left corner of the window.

Code:

instructions for use

			Option Explicit 
Public Marker() As Long 
--------------------------------------------------------------------------------------------------------- 
 
Public Function GoToRange(CellAddress As String, Optional ToSheet As Long) 
    If ToSheet = Empty Then ToSheet = ActiveSheet.Index 
     
    On Error GoTo InitializeMarker 
     'IF there is an address in the Marker array, then make room for another
    If Marker(UBound(Marker)) > 0 Then ReDim Preserve Marker(UBound(Marker) + 3) 
     
     'save the current sheet & top-left cell addresses
    Marker(UBound(Marker) - 2) = ActiveSheet.Index 
    Marker(UBound(Marker) - 1) = ActiveWindow.ScrollRow 
    Marker(UBound(Marker) - 0) = ActiveWindow.ScrollColumn 
     
     'cancel error trapping
    On Error GoTo 0 
     
     'go to the specified range
    Application.Goto Sheets(ToSheet).Range(CellAddress), True 
    Exit Function 
     
InitializeMarker: 
    ReDim Preserve Marker(2) 
    Resume Next 
End Function 
 
 '---------------------------------------------------------------------------------------------------------
 
Public Sub GoBack() 
     
    On Error Resume Next '< always keep the first address
     
     'go back to the previous address
    Application.Goto Sheets(Marker(UBound(Marker) - 2)) _ 
    .Cells(Marker(UBound(Marker) - 1), Marker(UBound(Marker) - 0)), True 
     
     'we're now back at the prev address, so remove this address from the Marker array
    ReDim Preserve Marker(UBound(Marker) - 3) 
     
End Sub

How to use:

Open an Excel workbook
Select Tools/Macro/Visual Basic Editor
In the VBE window, select Insert/Module
Copy and paste the code into the Module
Now select File/Close and Return To Microsoft Excel
Dont forget to save your changes...

Test the code:

Download the attachment and extract the workbook GoForward_GoBack to view some example uses.
Or, for a simple example...
Write a procedure and use GoToRange("A2000") as its sole code line
Run this procedure, note the location then run the procedure GoBack.

Sample File:

GoForward_GoBack.zip 11.73KB

Approved by mdmackillop

This entry has been viewed 185 times.