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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

AlphaFrog

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

Watch MrExcel Video

Forum statistics

Threads
1,122,496
Messages
5,596,497
Members
414,072
Latest member
2020914

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