Excel's HYPERLINK function
Excel's HYPERLINK function
(OP)
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
=A253
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
=HYPERLINK("#"&CELL("address",A253),A253)
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
=HYPERLINK("#"&ADDRESS(ROW(A253)+25,COLUMN(A253),4),A253)
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.
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
=A253
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
=HYPERLINK("#"&CELL("address",A253),A253)
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
=HYPERLINK("#"&ADDRESS(ROW(A253)+25,COLUMN(A253),4),A253)
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.





RE: Excel's HYPERLINK function
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Excel's HYPERLINK function
(I would rather avoid VBA for this task.)
RE: Excel's HYPERLINK function
RE: Excel's HYPERLINK function
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.
RE: Excel's HYPERLINK function
RE: Excel's HYPERLINK function
CODE
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
RE: Excel's HYPERLINK function
I had figured that he likely had some formatting difference in his headers anyway, but your is more foolproof.
RE: Excel's HYPERLINK function
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.
CODE
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
RE: Excel's HYPERLINK function
Why the reluctance? (looks like non-familiarity with VBA was not the problem).
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Excel's HYPERLINK function
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).
RE: Excel's HYPERLINK function
Stephen Argles
Land & Marine
www.landandmarine.com