Excel's HYPERLINK function 1

[Denial]

I am using Excel 2003. One of my more elaborate spreadsheets has an entire worksheet that consists of nothing more than a large amount of text devoted to documentation. The text is structured in sections, and each section has a heading in column A of an otherwise blank row preceding the body of the text on that subject.

Suppose for the purposes of the following discussion that one of my sections of text is headed "How to delete an item", and that this heading is in cell A253 of the worksheet (as text).

To make the mass of documentation slightly more understandable, I initially created a "table of contents" at the top of the worksheet. In this table's first and simplest form, the entry for the above hypothetical section consisted simply of a cell that contained the formula
[tt]=A253[/tt]
placed (say) in cell A9.

This was fine as far as it went, but later I decided I wanted to provide the ability for my user to click on cell A9 then have the screen view "move" so that it includes cell A253. This I achieved by placing in cell A9 the formula
[tt]=HYPERLINK("#"&CELL("address",A253),A253)[/tt]

An improvement. However, because this process involves movement down the worksheet, my selected cell ends up at the bottom of the screen. This means the user cannot see the associated body of text without scrolling down some more. I like to pamper my user, so I added a further refinement, changing the formula in cell A9 to
[tt]=HYPERLINK("#"&ADDRESS(ROW(A253)+25,COLUMN(A253),4),A253)[/tt]

A further improvement. My precious user can now see the first 25 lines of the text he is seeking without further exercise. But the selected cell is now at some non-distinguished line in the text, and the section heading is not at the top of the screen.

I am fully aware that "the best is the enemy of the good". However, contrary to Voltaire's dictum, I would ideally like to set things up so that when my user clicks on cell A9 the screen immediately jumps to a view where cell A253 is at the top left corner and is the selected cell.

I have a horrible suspicion that this will be impossible to achieve, but before I give up I thought I should offer this forum the opportunity to prove me wrong.

 

[IRstuff]

I don't know if you can do it directly from the spreadsheet, or whether you need to write a macro, but if you jump from the lower right of the spreadsheet, the target cell is at the upper left of the display.

TTFN

FAQ731-376

 

[Denial]

Yes. I discovered that a jump up the worksheet gives me exactly the result I am seeking. So perhaps I am looking for some way to hide two jumps behind the one mouse-click: the first to take me to a cell at a very large row number and a very large column number, then the second to immediately take me on to my target cell.

(I would rather avoid VBA for this task.)

 

[NomLaser]

I know that this is a PITA but what about trying to use Word and embed what you need from excel if you are doing calculations. Word has a built in TOC (Table of Contents) builder that you can easily create and maintain a TOC. Just a thought and if it doesn't fit then feel free to discard it.

 

[TDAA]

OK, thank you for the challenge, and I think this will work for you:

First, I would make the headings within the document different somehow. For what I did, I just changed the font size (12 in this case). Just make it something different than other cells you do not want to trigger the move (i.e. the only cells with this size are the headings).

Second, hit Alt F11 to open VBA (this code will not need user interaction), open the sheet you want it to run on from the list on the side, paste this in:

Private Sub Worksheet_SelectionChange(ByVal Target As range)
If Selection.Font.Size = 12 Then
With ActiveWindow
.ScrollRow = Target.Row
.ScrollColumn = Target.Column
End With
Else
End If
End Sub

This will check the font size, and if it matches, will move it to the upper left. If it is any other text size, it will not move it. This way, when your user clicks on any other cells, things will not shift on them.

You could use Bold, font, size, italics, or any other sort of item to change the trigger.

I set this up in Excel 2007, so some tweaks may be required.

 

[TDAA]

Sorry, I missed the non-VBA statement, but I think this is probably the easiest way to make it work: Write it once, make the headings unique, and the only thing the user has to do is approve the use of VBA.

 

[brengine]

To add to what TDAA provided...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim sLinkAddress As String
Dim rngLink As Range

    If (InStr(Target.Formula, "HYPERLINK") > 0) Then
        'Example cell formula is: =HYPERLINK("#"&CELL("address",A253),A253)
        'Next line will parse out this region-------------------^^^^
        sLinkAddress = Mid(Target.Formula, InStr(Target.Formula, ",") + 1, Len(Target.Formula) - InStrRev(Target.Formula, "),") - Len("),"))
        
        Set rngLink = Range(sLinkAddress)
    
        With ActiveWindow
            .ScrollRow = rngLink.Row
            .ScrollColumn = rngLink.Column
        End With
    End If
End Sub

Using this Procedure would require no modifications to your current WorkSheet (assuming your "HYPERLINK" formulas are all a consistent format...which they should be).

 

[TDAA]

Nice method to parse that. My skills do not cover as much VBA as I would like them to.

I had figured that he likely had some formatting difference in his headers anyway, but your is more foolproof.

 

[Denial]

Thanks, everyone. You've pretty much confirmed that a non-VBA solution does not exist. So, reluctantly, I resorted to VBA, with an event handler. I ended up requiring my user to DOUBLE-click in the entry in the index: the extra bit of exercise won't hurt him.

In case anyone is interested in my end result, I will include it below. It is not vastly different to what Brengine suggests above, but adds a bit of checking and bullet-proofing.

Option Explicit
Option Base 1

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'
'  When user double-clicks in the Index portion of this worksheet (as defined
'  by the named range "DocumentationIndex"), jump to the cell whose address
'  is given in the double-clicked-upon cell.
'
Dim OurFormula As String, I As Long
'
If Not Intersect(Target, Range("DocumentationIndex")) Is Nothing Then
    '
    '  Have double-clicked within the Index's range.
    '
    '  Cancel whatever double-clicking would normally do.
    '
    Cancel = True
    '
    '  Get the formula in the double-clicked cell, and extract from it the
    '  address we expect it to contain.
    '
    '  Normally any formula will be in the format "=C123".
    '
    '  Note that the use of "(1,1)" allows for merged cells.
    '
    OurFormula = Target(1,1).Formula
    If Mid(OurFormula, 1, 1) <> "=" Or Len(OurFormula) < 3 Then GoTo NoAddress
    OurFormula = Mid(OurFormula, 2, Len(OurFormula))
    '
    '  Check whether we now have a valid range.
    '
    On Error GoTo NotSimple
    I = Range(OurFormula).Rows.Count
    On Error GoTo 0
    GoTo HaveAddress
NotSimple:
    '
    '  We don't have a straight forward address.
    '
    '  This would be the place to parse the address out of other possible
    '  formula-formats we might want to allow for.  But not today.
    '
    GoTo NoAddress
    '
HaveAddress:
    '
    '  Now move so that the cell whose address was in the double-clicked cell
    '  is in the top left corner of the screen, and is selected.
    '  (Actually move to one row higher, because it give a better effect.)
    '
    ActiveWindow.ScrollRow = Range(OurFormula).Row - 1
    ActiveWindow.ScrollColumn = Range(OurFormula).Column
    Range(OurFormula).Select
End If
GoTo AllDone
'
NoAddress:
MsgBox "Cannot extract a valid address from cell contents " & Target(1,1).Formula
'
AllDone:
'
End Sub

 

[IDS]

So, reluctantly, I resorted to VBA, with an event handler.

Why the reluctance? (looks like non-familiarity with VBA was not the problem).

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Denial]

Because I intend to use the approach on several different spreadsheets, some of which are presently free of VBA. I don't like introducing VBA to a VBA-free spreadsheet unless it is completely unavoidable.

And in answer to your next question:
» Some users get nervous when they are prompted to allow macros;
» The spreadsheet will not run properly on some computers (like Jobsian ones, as opposed to Gatesian ones).

 

[StephenA]

You could set up the page as you want to see it and then name a range at the bottom of that page and hyperlink to that.

Stephen Argles
Land & Marine

http://www.landandmarine.com