Forcing Hyperlinks to Open in a new Window

[Bob Phillips]

Hi Sean,

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.