Forum Discussion
Positioning a row at the top of the window via an internal link
On a worksheet with many rows of text, I help the user navigate via internal links to topic-specific, named cells. The user just clicks on a topic to display the topic. This works very nicely except that the named cell is at the bottom of the worksheet window and the user has to scroll down to see the information he needs. How can I change this behavior so that the named cell is on the top row of the worksheet (regardless of the number of visible rows)?
Try this. Right click on your worksheet tab, select view code, and paste this into the code module.
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Dim cell As Range On Error Resume Next Set cell = ActiveSheet.Range(Target.SubAddress) If Not cell Is Nothing Then Application.Goto ActiveCell, Scroll:=True End If End Sub
5 Replies
RiverKing Perhaps the small piece of code in the attached link is helpful.
https://www.extendoffice.com/documents/excel/5117-excel-active-cell-top-screen.html
Thank you for your reply. I see how this would work but I'm afraid it isn't exactly what I need.
In my case, I am attempting to put documentation in one sheet to explain how a User Form works in another sheet. The documentation has a menu near the beginning of the sheet listing all of the topics on the sheet. Each menu item is a link to a cell at the beginning of the topic. When the user (whose knowledge of Excel can be next to nothing) clicks on a menu item, the topic title is displayed but at the bottom of the visible sheet and the user then has to scroll down to find the information he needs. It would be so much more useful if the topic title were displayed at the top of the visible sheet.
Try this. Right click on your worksheet tab, select view code, and paste this into the code module.
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Dim cell As Range On Error Resume Next Set cell = ActiveSheet.Range(Target.SubAddress) If Not cell Is Nothing Then Application.Goto ActiveCell, Scroll:=True End If End Sub
