Hyperlink Location

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ryan Parker - 20 Jun 2005 15:20 GMT

I have a spreadsheet with a bank of index buttons at the top that are linked
to separate series of data blocks listed below. The panes are frozen right
below the index buttons.

I have linked the top index buttons to a cell at the top of the appropriate
block of data below.

How can I make it so that clicking a hyperlinked index button makes the
linked cell appear at the top of the screen (ie, immediatedly underneath the
frozen panes line)

Reply to this Message

Bill Manville - 21 Jun 2005 00:57 GMT

Try this in the module of the worksheet:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
With ActiveWindow.Panes(ActiveWindow.Panes.Count)
.ScrollRow = Range(Target.SubAddress).Row
.ScrollColumn = Range(Target.SubAddress).Column
End With
End Sub

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Reply to this Message

SubSeaGuy - 23 Jun 2005 18:36 GMT

Bill,

I have the same problem but am using my HL to go from one worksheet to
another. I get an error in the

.ScrollRow = Range(Target.SubAddress).Row

statement. Is there a change I need to make?

SubSeaGuy

> Try this in the module of the worksheet:
>
[quoted text clipped - 8 lines]
> MVP - Microsoft Excel, Oxford, England
> No email replies please - respond to newsgroup

Reply to this Message

Bill Manville - 23 Jun 2005 23:46 GMT

Try this:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim iChar as Integer
Dim stAddr As String
stAddr = Target.SubAddress
iChar = InStr(stAddr,"!") ' Sheet!Range ?
If iChar>0 Then
Sheets(Left(stAddr,iChar-1)).Activate
stAddr = Mid(stAddr, iChar+1)
End If
With ActiveWindow.Panes(ActiveWindow.Panes.Count)
.ScrollRow = Range(stAddr).Row
.ScrollColumn = Range(stAddr).Column
End With
End Sub

I think it should work for links to any sheet in the workbook

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Reply to this Message

SubSeaGuy - 25 Jun 2005 09:03 GMT

Bill,

That did the trick. Relly appreciate your help.

SubSeaGuy

> Try this:
>
[quoted text clipped - 18 lines]
> MVP - Microsoft Excel, Oxford, England
> No email replies please - respond to newsgroup

Reply to this Message

SubSeaGuy - 25 Jun 2005 18:41 GMT

Bill,

Sorry to be so worrysome. The sub worked great when the hyperlink was cell
text with a hyperlink attached to it. I linked a hyperlink to a text box and
it seems to ignore the sub????? Any thoughts?

SubSeaGuy

> Try this:
>
[quoted text clipped - 18 lines]
> MVP - Microsoft Excel, Oxford, England
> No email replies please - respond to newsgroup

Reply to this Message

Bill Manville - 26 Jun 2005 08:45 GMT

I guess the Sheet_FollowHyperlink event only occurs for hyperlinks in
cells, not in objects on the sheet, which doesn't surprise me.

You could assign a macro to the textbox instead of using a hyperlink,
e.g.

Sub GoSomePlace()
Application.Goto Sheets("SomeSheet").Range("SomeNameOrAddress"),
Scroll:=True
End Sub

OR, for links which go to another sheet, if you were happy that every
time you activate the destination sheet the active cell is scrolled to
the top left-hand corner, put this in the module of that sheet:

Private Sub Worksheet_Activate()
Application.Goto ActiveCell, True
End Sub

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Reply to this Message