Hyperlink to same viewing position

[mcjohn]

Hi, I'm using excel 2007.

At the top of a spreadsheet I have hyperlinks to various cells in column A. The hyperlinks are in the first 10 rows that I froze the panes for to always show up top. When I click on a hyperlink while scrolled to the top of the page it jumps to a cell down the spreadsheet displaying everything above it that fits on a monitor. But while now scrolled down the spreadsheet if I then click another hyperlink from the top rows visible in the frozen pane it jumps to a cell but now displaying everything below it that fits on a monitor.

Is there anyway, VB or otherwise, to ensure the hyperlinks clicked always jump to either the top or the bottom?

Thank you,
John

 

[AlphaFrog]

This puts the Hyperlinked cell always at the top

• Right-click on the sheet tab
• Select View Code from the pop-up menu
• Patse the code below in the VBA edit window

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    ActiveWindow.ScrollRow = ActiveCell.Row
End Sub

 

[mcjohn]

Thanks!

 

[Cease]

I've done this on a lot of sheets and I have it select a range that's a lot taller than it would need to be.

Let's say you wanted to see a chart on the page from b28 to f30, I'd set the hyperlink to a28:a58

that will always have a28 be the top left, regardless where you started from.

Hope this makes sense, this little trick helped me a lot - and I love using frozen panes as a menu (of sorts) up top :D

 

[mcjohn]

Awesome trick, I can't believe I never thought of that. Thanks!