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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,222,195
Messages
6,164,509
Members
451,900
Latest member
lamski

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
Back
Top