You can do what you asked for using VBA code and a tool bar button. First,
we will install the code so that it is in place when we add the button.
Press Alt+F11 to take you into the VBA editor. Once inside the editor, look
over at the left side of the screen where you will see the Project Window
and double-click the entry marked "ThisWorkbook". Doing that will bring up
the code window for the workbook itself. Copy/Paste the following code into
that code window....
'*************** START OF CODE ***************
Dim LastCells As New Collection
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
LastCells.Add Sh.Name & Chr$(1) & Target.Address
If LastCells.Count > 101 Then LastCells.Remove 1
End Sub
Sub GoBack()
With LastCells
If .Count > 1 Then
LastCells.Remove .Count
Worksheets(Left$(.Item(.Count), InStr(.Item(.Count), _
Chr$(1)) - 1)).Select
Range(Mid(.Item(.Count), InStr(.Item(.Count), Chr$(1)) + 1)).Select
If .Count > 0 Then LastCells.Remove .Count
End If
End With
End Sub
'*************** END OF CODE ***************
Now, go back to the worksheet and right-click on any toolbar and select
Customize from the popup menu that appears. Click the Commands tab on the
dialog box that appears, scroll the Categories list down to the Macros item
and click it. In the Commands list, you should see a "happy face" button
labeled Custom Button... click-drag that button onto a toolbar someplace
(the drag-icon will show a plus sign at locations where you can place it).
Next, right-click the "happy face" icon you just placed on the toolbar and
select "Assign Macro" from the popup list that appears. Click on the
ThisWorkbook.GoBack entry and then click the OK button. While you are there,
you can right-click the "happy face" icon again and select "Change Button
Image" to change the happy face to some other image. Once you have finished,
click the Close button on the Customize dialog box and you are done. As you
click around from cell to cell on the current or any other worksheet, those
locations will be stored. Clicking the Macro Button you added will retrace
your path back through the last 100 cells you visited. As you back up
through cells, the cells you back up through are removed from the list...
that is, you cannot go forward through the list again... it is only a
backward-running list.
Rick
