Consistently have a hyperlink-targeted cell appear in the upper-left corner in Excel

Hello,

When using a hyperlink in Excel (2007) to go to another cell (in either the same or a different worksheet but in the same workbook), is there an easy way to consistently have the target cell end up in the upper left corner of the screen (Excel window)?

As it is now when using a hyperlink, the target cell ends up being positioned in one of the four corners based on the location of the last active cell in that worksheet.

For example, suppose that when a maximized Excel worksheet is on your monitor, you can see columns A-Z and rows 1-50.  Furthermore, suppose you have a table of data, 50 rows tall and Z columns wide with the upper left corner of the table located at CA501 (yellow cell in Fig. 1). That would mean that the table range would be CA501:CZ550 (blue area in Fig. 1) and just fit on your screen.
 1  

Now suppose that you want to be able to quickly get the complete table on your screen from many different locations in your workbook.  Obviously, the simple solution is to select the full range (CA501:CZ550) and give it some name which then can be the target for any number of hyperlinks throughout the workbook.

However, for reasons I will not go into, suppose that you cannot define the entire range but only the single cell in the upper left corner (CA501).  With this being the case, your hyperlinks target only CA501 and you may or may not end up with the table displayed on your screen.

For instance, suppose your last active cell in that worksheet is A1 (a position up and to the left of your target cell).  When the hyperlink is activated, it only moves you to where the target cell, CA501 (yellow cell in Fig. 2), just barely shows up at the bottom right corner of your screen. Thus, your screen displays a range of cells up and to the left of the target cell (green area in Fig. 2) but none of the table can be seen.
 2  


Similarly, if the last active cell is up and to the right, hyperlinking results in the target cell appearing in the bottom left corner of your screen and the range displayed on your screen is up and to the right (red area in Fig. 3).  You might see the top row of your table at the bottom of your screen but the rest of it is off-screen below.
 3
The table is correctly and fully displayed on the screen only when the hyperlink "jump" comes from a last active cell which is located down and to the right of the target cell.  Hence my initial question:  "Is there an easy way to consistently have the target cell end up in the upper left corner of the screen (so the range down and to the right is what appears on the screen)?

The only solution I've thought of is to utilize the following steps:

1.  name some cell (let's call it "BOUNCE") way down and to the right (that will never be used for anything else in that worksheet and will always be below and to the right of any data)
2.  create a macro which always jumps to the named cell first and then from there, jumps to the target cell.

An example of where this approach has been very effective is in a directory I created with several hundred names sorted alphabetically.  I first divided the directory into groups -- one for each letter in the alphabet -- and gave each group a header row with the first cell in each header having the letter of that group (e.g. - A -, - B -, -C-, and so on).  

Next, from the Insert menu, I chose a button shape which I duplicated 26 times and gave each one a letter.  Finally, each button was assigned a macro which jumped first to the BOUNCE cell and then to the desired letter. Because the second jump is always coming from down and to the right, the targeted header always appears in the upper left corner so that the screen is filled with only those names beginning with that letter.

The buttons are arranged side-by-side in three rows so any time I find myself working in one specific worksheet for an extended period of time, I can simply copy the 26 buttons from my directory and paste them wherever I want and in so doing, be only a single click away from a desired letter or group of names in my directory.


Now though, I've got a much, much larger project with far more "bounce" links to create and it would sure be nice to get the same result without having hundreds or even thousands of macros.

It would be great if anyone has a suggestion that could simplify what I have described.

Incidentally, another part of this question is described here:  Run Excel macro by clicking a designated cell

Thanks
Steve_BradyAsked:
Who is Participating?
 
byundtCommented:
Steve,
You might try using a Workbook_SheetFollowHyperlink event sub. It runs when the hyperlink is clicked and can set the ScrollRow and ScrollColumn properties of the ActiveWindow. It must be installed in the ThisWorkbook code pane. As written, the code will fail if the hyperlink goes outside the workbook.
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
Dim ws As Worksheet
Dim rg As Range
Dim i As Long
Dim sAddr As String
sAddr = Target.SubAddress
If Left(sAddr, 1) = "'" Then sAddr = Mid(Replace(sAddr, "'!", "!"), 2)
i = InStr(1, sAddr, "!")
Set ws = IIf(i = 0, ActiveSheet, Worksheets(Left(sAddr, i - 1)))
Set rg = ws.Range(IIf(i = 0, sAddr, Mid(sAddr, i + 1)))
ActiveWindow.ScrollRow = rg.Row
ActiveWindow.ScrollColumn = rg.Column
End Sub

Open in new window

Brad
0
 
Patrick MatthewsCommented:
Steve,

If you happened to know that a particular worksheet had hyperlinks leading to, say, B7, D28, and Z999, then you could add this code to that worksheet's code module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Dim cel As Range
    
    Const CellAddrs As String = "|$B$7|$D$28|$Z$999|"
    
    Set cel = Target.Cells(1)
    If InStr(1, CellAddrs, "|" & cel.Address & "|") > 0 Then
        With ActiveWindow
            .ScrollColumn = cel.Column
            .ScrollRow = cel.Cells(1).Row
        End With
    End If
    
End Sub

Open in new window


Patrick
0
 
Steve_BradyAuthor Commented:
Thanks for the response.

If you happened to know...

Can you explain what you mean by "happened to know"?  The links of a particular sheet can be identified at any point in time but other links are being created ongoing so there would need to be a way to update the list.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Patrick MatthewsCommented:
Nice suggestion, Brad, I had forgotten about that event.

Does that only work for "hard" hyperlinks, or does it also work if the hyperlink was created with the HYPERLINK() function?

Patrick
0
 
zorvek (Kevin Jones)ConsultantCommented:
Brad, I think this is a little easier than that. The Address property of the Target object is empty if the target is within the workbook. Thus this seems to work in all cases I tried.

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)

    If Len(Target.Address) = 0 Then
        ActiveWindow.ScrollRow = ActiveCell.Row
        ActiveWindow.ScrollColumn = ActiveCell.Column
    End If

End Sub

Kevin
0
 
byundtCommented:
In response to Patrick's comment, the HYPERLINK function does not appear to trigger the Workbook_SheetFollowHyperlink event in Excel 2010. Hyperlinks that are inserted using the Insert...Hyperlink menu item do trigger it (and that's how I was testing my code).

It looks like Kevin made a nice improvement to my suggested code.

0
 
Steve_BradyAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.