Create Hyperlink in VBA

[PPL]

Excel 2002 / 2003.
I'm trying to create a hyperlink in cell A19 such that the result opens in a
new window. I'm using

Range("A19").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="c:\test.htm",
NewWindow:=True, AddHistory:=True

I'm getting Runtime Error 1004: Application defined or Object defined error

Anyone any idea where am I going wrong ???

TIA

 

[Dave Peterson]

When I look at VBA's help for the Add method for Hyperlinks, I see this:

Add method as it applies to the Hyperlinks object.

Adds a hyperlink to the specified range or shape. Returns a Hyperlink object.

expression.Add(Anchor, Address, SubAddress, ScreenTip, TextToDisplay)
expression Required. An expression that returns a Hyperlinks object.

Anchor Required Object. The anchor for the hyperlink. Can be either a Range
or Shape object.

Address Required String. The address of the hyperlink.

SubAddress Optional Variant. The subaddress of the hyperlink.

ScreenTip Optional Variant. The screen tip to be displayed when the mouse
pointer is paused over the hyperlink.

TextToDisplay Optional Variant. The text to be displayed for the hyperlink.


======
I don't see NewWindow or addhistory parms.

I think you've gotten these parms confused with .FollowHyperlink's parms.

 

[Brian]

Try....

Sub Test2()

ActiveSheet.Range("A19").Select
With ActiveSheet
.Hyperlinks.Add Anchor:=Selection, Address:= _
"c:\1\test.htm", TextToDisplay:="Test"
End With
End Sub

 

[PPL]

Thanks Brian,
That works well. However I need the file to open in a new window.

I've tried appending NewWindow:=True to your Hyperlinks.Add method as
follows:

ActiveSheet.Range("A19").Select

With ActiveSheet
.Hyperlinks.Add Anchor:=Selection, Address:= _
"c:\1\test.htm", TextToDisplay:="Test", NewWindow:="True"
End With
End Sub

I'm getting Runtime Error 1004: Application defined or Object defined error.

Is there any other way of achieving the same result please?

Thanks again
Phil

 

[PPL]

Thanks Dave,
Maybe I need to look at the FollowHyperlink method.
Thanks again for your response.

Phil

 

[Brian]

To be honest I am not sure why it would not open it in a new window. It does
for me, I wonder if that is just something to do with the brower where it is
set to automatically open new windows as a new tab if a window is already
open? As I said, mine opens fine so I do not think it is a excel issue.

Also, as far as I know newwindow is not a valid variable for the hyperlinks
function.

 

[PPL]

Thank you to those who replied.
I've found a work-around to the problem of not being able to configure a
hyperlink in Excel to open in a new browser window. Essentially I used an
excel rectangular autoshape drawing object. placed it over the cell that
requires the hyperlink.
Formatted the rectangle w/ white border & no fill (i.e. Transparent)
Removed the hyperlink in the cell & formatted the text in blue w/ underline
Right click the rectangle autoshape > Assign Macro

Sub MyHyperlinkInNewWindow
Dim KB_ArticlePath As String
KB_ArticlePath = "http:/anarticle.htm"
ThisWorkbook.FollowHyperlink Address:=KB_ArticlePath, NewWindow:=True
End sub

That's it. It works like a charm.
Not fun for anyone else who wants to change the link because a casual look
at it and you'd never know that it's anything else than ... well .. a
hyperlink!

Phil

 

[Dave Peterson]

You could plop a button from the Forms toolbar in row 1 of your worksheet.

Then freeze the window (Window|Freeze Panes (in xl2003 menus) so that row 1 is
always visible.

Then assign your macro to the button.

But change the macro slightly.

Option Explicit
Sub MyHyperlinkInNewWindow()
With ActiveCell
If .Hyperlinks.Count > 0 Then
ThisWorkbook.FollowHyperlink _
Address:="http://" & .Value, _
NewWindow:=True
End If
End With
End Sub


This will only run if the user selects the cell and clicks on the button--not if
the user clicks on the hyperlink itself.

You could remove the hyperlink and use some other indicator (value starts with
an HTTP://???).

Option Explicit
Sub MyHyperlinkInNewWindow()
With ActiveCell
If UCase(Left(.Value, 7)) = UCase("http://") Then
ThisWorkbook.FollowHyperlink _
Address:=.Value, _
NewWindow:=True
End If
End With
End Sub

(notice that the "http://" was dropped in the second suggestion, while the first
suggestion added it to the hyperlink address.)