Dec 08 2020 07:59 PM
Dec 08 2020 07:59 PM
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)?
Dec 08 2020 10:32 PM
@RiverKing Perhaps the small piece of code in the attached link is helpful.
Dec 09 2020 11:15 AM
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.
Dec 09 2020 01:20 PMSolution
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
Dec 10 2020 03:30 PM
You are my Hero! Your solution works like magic just as you coded it and is exactly what I was looking for.
I have a question or two though: Is the "On Error Resume Next" line really necessary. What would cause the "Set cell ..." line to raise an error and what would happen in that case if the "On Error Resume Next" were omitted?
Even though it might cause my users to tar and feather me as a practitioner of the black arts, I thank you for your response.
Dec 10 2020 03:57 PM