Forcing Hyperlinks to Open in a new Window

[Sean_OL]

Hi,

I have been building a reporting application in Excel using VBA. All the data is updated by me and locked, then people can view it a bunch of different ways using the views I have built.

The spreadsheet itself should be posted on our intranet, allowing people to view it. I initially had it posted as a link to the Excel spreadsheet, which allowed people to open it in their browsers. However I found a lot of unexpected behaviour when the spreadsheet opened in a browser. I have fixed some of the problems since, but have one or two remaining.

My main problem at the moment is hyperlinks. We have a document containing instructions and such posted on our webpage. I simply added a link to the document to the top right of most pages. However when you click on the link in a web browser, it tried to open it in the same browser.

This leads to a 'do you want to save changes' message, then if you hit 'back' in your browser after navigating away, the file reopens from scratch (I have a macro that hides a bunch of sheets on activation).

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.

Any advice would be appreciated.

Sean

[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.

[Sean_OL]

Thanks a million,

That worked fine, with a few tweaks.

For Some Reason the Target.Address value came into the routine as blank. But the Target.Screentip value works. Which is actually better, the Screen Tips will be consistent for each link.

The other thing was it still prompted to save changes, even thought the contents of the window didn't change. I just added a bit of code to make it think it was already saved.



Thanks for the help again.

[Bob Phillips]

That worked fine, with a few tweaks.

Great, I have already used it again today .

For Some Reason the Target.Address value came into the routine as blank. But the Target.Screentip value works.

What I actually meant was

Select Case Target.Range.Address(False, False)

Screentip is a blank property for me. You could also use SubAddress, which would looke like Sheet1!A1.