Solved: Undo a Hyperlink

[oliver]

I have a workbook that has one sheet that requires user input and it refers to a number of statutes. On a second sheet I have all of those statutes.(60 or more pages)
I have used a hyperlink on sheet 1 to go to the statute on sheet 2. That was easy and works great. The problem: Is there an easy way to go back to the sheet at the point where i clicked on the hyperlink? The undo button is disabled.
My VBA knowledge is limited, but I'll take any suggestions I can get.
Thanks
Oliver

[marshybid]

Hi there, you just need to create a hyperlink cell on sheet 2 which references the point (cell) in sheet 1 that you originally linked from.

Maybe post your workbook for reference.

Marshybid

[TomSchreiner]

My first thought would be to create a floating custom commandbar with a button named "Return" or something along those lines. Perhaps OnKey or a macro with a keboard shortcut in place of the commandbar. In the onaction or procedure you could use code such as:

Air code...
With Application
.Goto .PreviousSelections(1)
End With

[Kenneth Hobs]

This method only works after the first hyperlink is clicked.

Right the sheet and View Code. Paste this and change the sheet name if not Sheet1.
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
SaveSetting "GotoLastCell", ThisWorkbook.Name, "Address", "Sheet1!" & Target.Range.Address
End Sub

Paste this in a Module:
Sub GotoLastCell()
Dim r() As String
r() = Split(GetSetting("GotoLastCell", ThisWorkbook.Name, "Address"), "!")
Application.Goto Worksheets(r(0)).Range(r(1)), False
End Sub

Finally, add a button to run GotoLastCell. I use the Control Toolbox toolbar button:
Private Sub CommandButton1_Click()
GotoLastCell
End Sub

I like to use Windows > Freeze Pane, and put the buttons like this in column A.

[oliver]

Thanks to all for the suggestions. Ken, your code works great for what I am doing.