Excel vba Hyperlink help again

[wahmed]

Dear Fellows.

Could you please help me regarding Excel vba hyperlink Cell reference ?

I have many sheets in a workbook and each sheet is linked to other but my problem is that when ever I follow hyperlink for going to next sheet and when come back again with back hyperlink then set focus cell is A1 in sheet, is there anyway that when every I go to next sheet and then come back to main sheet with back help then I get my cell focus same cell inwhich I was before instead of going top of sheet as A1 ...

Could you please help me ?

regards

Wahmed

 

[Jerry Sullivan]

Hello Wahmed,

One option would be to create a Named Range that holds a reference to the last Hyperlinked range clicked.

To do this:
1. Make a Range named: ReturnLinkAddress

The initial address can be any cell, since this will change each time a Cell with a Hyperlink is selected.

2. Next, edit your "Return Back" hyperlinks to go to: ReturnLinkAddress
(In the Edit Hyperlinks dialog box click on ReturnLinkAddress in the Defined Names section.)

3. Lastly, add this VBA Event Code to the Sheet Code of any Sheets that have Hyperlinks that you want to return to.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Hyperlinks.Count = 1 Then
        ActiveWorkbook.Names("ReturnLinkAddress").RefersTo = _
            Target
    End If
End Sub

That's a lot of steps, so just let me know if you get stuck.

Good luck!