Hi Sean,
Originally Posted by
Sean_OL
So all that rambling aside, how do I force the hyperlinks to open a new window, and not mess with the Excel window? I have looked a bit at the BEfore_Hyperlink property, but by the time that kicks in, the prompt to save is already showing.
Try this approach.
For all of your hyperlinks, change the text to something meanigful, and the hyperlink to itself, that is the same cell in the same worksheet.
Then add this code
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
With ThisWorkbook
Select Case Sh.Name
Case "Sheet1"
Select Case Target.Address(False, False)
Case "A1":
.FollowHyperlink Address:="http://xldynamic.com", NewWindow:=True
Case "A2":
.FollowHyperlink Address:="http://microsoft.com", NewWindow:=True
'etc.
End Select
Case "Sheet2"
Select Case Target.Address(False, False)
'etc.
End Select
'etc.
End Select
End With
End Sub
This goes in the Thisworkbook code module.
If you can have the web address in the cell, you can simplify the code to just
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
ThisWorkbook.FollowHyperlink Address:=Target.Name, NewWindow:=True
End Sub
The important thing is to reference the link back to the calling cell so that the page is invoked when you wan t it.