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)

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

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

"Bill Manville" wrote:

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

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 iChar0 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

Bill,

That did the trick. Relly appreciate your help.

SubSeaGuy

"Bill Manville" wrote:

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 iChar0 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

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

"Bill Manville" wrote:

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 iChar0 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

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