FollowHyperlink doesn't actually follow =Hyperlink formula

[thunderhawk6630]

I have an estimating spreadsheet that we use to estimate jobs. There are 80 sheets, all identical.

The sheets can be quite long, and to prevent scrolling, I froze the top row, and added some hyperlinks (using the formula =hyperlink). The links I have are Top, Materials, Labor, Summary Subcontract. Clicking on them jumps the screen down to that section.

The links work, but what I want to have happen is when I click the link, the cell that is highlighted, is brought up to the top left of the screen, currently it is at the bottom.

Through all my testing, it seems that the followhyperlink command does not actually follow the formula method of inserting hyperlinks. I could go through the entire workbook and hard link everything to the correct spot. But that would take some time, and make it hard to make changes in the future. However I do have working code to make the hyperlinked cell appear in the top left if I hard code the link. See code below. I simply put this in the Woorkbook, rather than the Module and it works great.

'This code makes hyperlinks appear at the top of the page, not the bottom
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
    ActiveWindow.ScrollRow = ActiveCell.Row
End Sub

So my question is, is there a way to get excel to treat the formula method of links, the same as the inserted way? Is there some clever workaround? If works if I use SelectionChange, but then anything I click jumps to the top.

Thanks,

 

[jkpieterse]

If your hyperlinks are always on row 1, this appears to work:

Dim moPrevCell As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Row = 1 Then
        Set moPrevCell = Target.Cells(1, 1)
    Else
        If Not moPrevCell Is Nothing Then
            ActiveWindow.ScrollRow = ActiveCell.Row
        End If
        Set moPrevCell = Nothing
    End If
End Sub

 

[thunderhawk6630]

jkpieterse, your code worked beautifully. Thank you very much. I did make one change to the code, and that was to make is so it would apply to all workbooks, rather than have to place it in every sheet. Works fantastic.

Dim moPrevCell As Range


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Row = 1 Then
        Set moPrevCell = Target.Cells(1, 1)
    Else
        If Not moPrevCell Is Nothing Then
            ActiveWindow.ScrollRow = ActiveCell.Row
        End If
        Set moPrevCell = Nothing
    End If
End Sub

 

[thunderhawk6630]

jkpieterse, if I wanted to target a range (M1:T1) instead of the entire 1st row, what changes would I need to make? I've tried a couple ways and I can't seem to make it work.

The issue is when I hyperlink to another worksheet, Excel automatically highlights cell A1, since that cell is in the first row, when I click out of it into another cell, that cells row get jumped to the top. I would like to try to avoid this.

Thanks,