Erin

In my experience, you can't do this other than with the FollowHyperlink
event.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Erin" wrote in message
...
I am creating a hyper-link on each cell using the
hyperlinks.add method
I want this hyper-link to open in a new window, but don't
see a property for this?

The only place I can see to force it to open in a new
window is with doing a hyperlinks.follow, but I have to
code this as an event on the spreadsheet. I would rather
have the hyperlink already on the spreadsheet that Excel
handles the user interface.

Erin

One way to do it is to point each hyperlink back to the cell that contains
it. Then change the TextToDisplay to the actual web page. If the hyperlink
is in C8, then you might use this to Add

Sheet1.Hyperlinks.Add Sheet1.Range("c8"), "Sheet1!C8", , ,
"http:\\www.dicks-clicks.com"

Note that the Anchor and Address properties refer to the same place. If
they don't, then you will get two browser windows opened; the one from the
hyperlink and the one from the FollowHyperlink event. The text to display
is used to store the actual address to which you want to navigate. The
FollowHyperlink event will then look like this:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Me.Parent.FollowHyperlink Target.TextToDisplay, , True

End Sub

If you can't put the address in the TextToDisplay property, then you will
have to find another place to store the actual address. You could create a
table on a hidden sheet that maps the range address to the web address, such
as

C8 http:\\www.dicks-clicks.com
C10 http:\\www.cpearson.com
C12 http:\\www.j-walk.com

In that case, your FollowHyperlink sub would look like this

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Dim FndAdd As Range

Set FndAdd = Sheets("Hidden").Columns(1).Find(Target.Range.Addr ess(0, 0))

If Not FndAdd Is Nothing Then
Me.Parent.FollowHyperlink FndAdd.Offset(0, 1).Value
End If

End Sub


--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Erin" wrote in message
...
Okay then... anyone know how to capture the event when a
user clicks on a hyper-link? I really don't want to have
to do this with double-clicks. I prefer using the
hyperlink so that it's shown in blue and then cursor
changes so the user knows its a hyper-link.

-----Original Message-----
Erin

In my experience, you can't do this other than with the
FollowHyperlink
event.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Erin" wrote in message
...
I am creating a hyper-link on each cell using the
hyperlinks.add method
I want this hyper-link to open in a new window, but
don't
see a property for this?

The only place I can see to force it to open in a new
window is with doing a hyperlinks.follow, but I have to
code this as an event on the spreadsheet. I would
rather
have the hyperlink already on the spreadsheet that Excel
handles the user interface.


.