Forum Discussion
Positioning a row at the top of the window via an internal link
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
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 SubYou 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.
- If the hyperlink were to an external file, then the subaddress is empty and you will get an object-defined error (1004).