SOLVED

Positioning a row at the top of the window via an internal link

%3CLINGO-SUB%20id%3D%22lingo-sub-1969789%22%20slang%3D%22en-US%22%3EPositioning%20a%20row%20at%20the%20top%20of%20the%20window%20via%20an%20internal%20link%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1969789%22%20slang%3D%22en-US%22%3E%3CP%3EOn%20a%20worksheet%20with%20many%20rows%20of%20text%2C%20I%20help%20the%20user%20navigate%20via%20internal%20links%20to%20topic-specific%2C%20named%20cells.%26nbsp%3B%20The%20user%20just%20clicks%20on%20a%20topic%20to%20display%20the%20topic.%26nbsp%3B%20This%20works%20very%20nicely%20except%20that%20the%20named%20cell%20is%20at%20the%20bottom%20of%20the%20worksheet%20window%20and%20the%20user%20has%20to%20scroll%20down%20to%20see%20the%20information%20he%20needs.%26nbsp%3B%20How%20can%20I%20change%20this%20behavior%20so%20that%20the%20named%20cell%20is%20on%20the%20top%20row%20of%20the%20worksheet%20(regardless%20of%20the%20number%20of%20visible%20rows)%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1969789%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1969903%22%20slang%3D%22en-US%22%3ERe%3A%20Positioning%20a%20row%20at%20the%20top%20of%20the%20window%20via%20an%20internal%20link%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1969903%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F247209%22%20target%3D%22_blank%22%3E%40RiverKing%3C%2FA%3E%26nbsp%3BPerhaps%20the%20small%20piece%20of%20code%20in%20the%20attached%20link%20is%20helpful.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F5117-excel-active-cell-top-screen.html%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F5117-excel-active-cell-top-screen.html%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1972943%22%20slang%3D%22en-US%22%3ERe%3A%20Positioning%20a%20row%20at%20the%20top%20of%20the%20window%20via%20an%20internal%20link%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1972943%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20reply.%26nbsp%3B%20I%20see%20how%20this%20would%20work%20but%20I'm%20afraid%20it%20isn't%20exactly%20what%20I%20need.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20my%20case%2C%20I%20am%20attempting%20to%20put%20documentation%20in%20one%20sheet%20to%20explain%20how%20a%20User%20Form%20works%20in%20another%20sheet.%26nbsp%3B%20The%20documentation%20has%20a%20menu%20near%20the%20beginning%20of%20the%20sheet%20listing%20all%20of%20the%20topics%20on%20the%20sheet.%26nbsp%3B%20Each%20menu%20item%20is%20a%20link%20to%20a%20cell%20at%20the%20beginning%20of%20the%20topic.%26nbsp%3B%20When%20the%20user%20(whose%20knowledge%20of%20Excel%20can%20be%20next%20to%20nothing)%20clicks%20on%20a%20menu%20item%2C%20the%20topic%20title%20is%20displayed%20but%20at%20the%20bottom%20of%20the%20visible%20sheet%20and%20the%20user%20then%20has%20to%20scroll%20down%20to%20find%20the%20information%20he%20needs.%26nbsp%3B%20It%20would%20be%20so%20much%20more%20useful%20if%20the%20topic%20title%20were%20displayed%20at%20the%20top%20of%20the%20visible%20sheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1973576%22%20slang%3D%22en-US%22%3ERe%3A%20Positioning%20a%20row%20at%20the%20top%20of%20the%20window%20via%20an%20internal%20link%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1973576%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F247209%22%20target%3D%22_blank%22%3E%40RiverKing%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this.%20Right%20click%20on%20your%20worksheet%20tab%2C%20select%20view%20code%2C%20and%20paste%20this%20into%20the%20code%20module.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_FollowHyperlink(ByVal%20Target%20As%20Hyperlink)%0A%20%20%20%20%20Dim%20cell%20As%20Range%0A%20%20%20%20%20%0A%20%20%20%20%20On%20Error%20Resume%20Next%0A%20%20%20%0A%20%20%20%20%20Set%20cell%20%3D%20ActiveSheet.Range(Target.SubAddress)%0A%0A%20%20%20%20%20If%20Not%20cell%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20%20%20Application.Goto%20ActiveCell%2C%20Scroll%3A%3DTrue%0A%20%20%20%20%20End%20If%0A%20%20%20%20%20%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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)?

5 Replies

@Riny_van_Eekelen

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.

best response confirmed by RiverKing (Occasional Contributor)
Solution

@RiverKing 

 

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

@JMB17 

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.

If the hyperlink were to an external file, then the subaddress is empty and you will get an object-defined error (1004).