Hyperlink to same viewing position

mcjohn

New Member
Joined
Sep 30, 2009
Messages
45
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,450
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

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

Cease

Board Regular
Joined
Dec 28, 2010
Messages
112
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,423
Messages
5,837,152
Members
430,479
Latest member
mamush200

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top