Consulting

Results 1 to 4 of 4

Thread: Forcing Hyperlinks to Open in a new Window

  1. #1
    VBAX Newbie
    Joined
    Jun 2005
    Posts
    4
    Location

    Forcing Hyperlinks to Open in a new Window

    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

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,149
    Location
    Hi Sean,

    Quote 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

    [VBA]
    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
    [/VBA]

    This goes in the Thisworkbook code module.

    If you can have the web address in the cell, you can simplify the code to just

    [VBA]
    Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
    ThisWorkbook.FollowHyperlink Address:=Target.Name, NewWindow:=True
    End Sub
    [/VBA]

    The important thing is to reference the link back to the calling cell so that the page is invoked when you wan t it.

  3. #3
    VBAX Newbie
    Joined
    Jun 2005
    Posts
    4
    Location
    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.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,149
    Location
    Quote Originally Posted by Sean_OL
    That worked fine, with a few tweaks.
    Great, I have already used it again today .

    Quote Originally Posted by Sean_OL
    For Some Reason the Target.Address value came into the routine as blank. But the Target.Screentip value works.
    What I actually meant was

    [VBA]
    Select Case Target.Range.Address(False, False)

    [/VBA]

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •