Follow link from Window("1") in Window("2") possible?

[Dennis]

2003

Attempting to effectively use two windows currently "tiled"

Prefer a link "Sheet1"; Window("xxx:1") be followed in Window("xxx:2")
on "Sheet7"

Windows("Book.xls:1").Activecell.Hyperlinks(1).Follow NewWindow:=True,
AddHistory:=True

I activated Windows("Book.xls:2") and attempted the above code
in the immediate window but the "follow" occured in
Windows("Sheet1:1").

Appreciate a response ASARP Thank you!!

Dennis

 

[NickHK]

Dennis,
From a bit of testing, it seems hyperlinks (and .Goto) always operate on the
window with .WindowNumber=1 rather than the ActiveWindow.
Also, the FollowHyperLink event fires after the new selection is made, so
you cannot cancel it the hyperlink.
The Windows collection behaves differently to other collections in that the
(index) changes;
<From Help>
Note that the active window is always Windows(1).
</From Help>
So you need to create the caption and use that.

This seems to work by faking a hyperlink (its actual address is the cell
that holds it, but the .TextToDisplay is in the form
"WindowNumber!Sheetname!Range"
e.g. "2!Sheet2!A3", but you can use any delimiter you want, as it has no
special meaning here

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim WindowText As String

WindowText = ThisWorkbook.Name & ":" & Split(Target.TextToDisplay, "!")(0)
Windows(WindowText).Activate

With Worksheets(Split(Target.TextToDisplay, "!")(1))
.Activate
.Range(Split(Target.TextToDisplay, "!")(2)).Select
End With

End Sub

Others may have a better way.

NickHK