PDA

View Full Version : Hyperlink



DarReNz
10-23-2005, 10:50 PM
Hi,

I would like to ask how do I insert a hyperlink in a middle of the cell ?

eg.
Cell A2
Read this link at [hyperlink] http://blabla.com [hyperlink]

I want the [hyperlink] to be underlined only. Any help ? Thanks

Killian
10-24-2005, 02:39 AM
You would use the add method for the worksheet's Hyperlinks collection (see Excel VBA help for full details)
If you just want the address to be formatted, you'll then have to change the formatting of the other characters in the display text back to normalDim strLink As String

strLink = "http://blabla.com"

Worksheets(1).Hyperlinks.Add Anchor:=Worksheets(1).Range("A2"), _
Address:=strLink, _
TextToDisplay:="Read this link at " & strLink
With Worksheets(1).Range("A2").Characters(1, Len(Worksheets(1).Range("A2").Text) - Len(strLink)).Font
.Underline = xlUnderlineStyleNone
.ColorIndex = xlColorIndexAutomatic
End With

Bob Phillips
10-24-2005, 03:25 AM
You would use the add method for the worksheet's Hyperlinks collection (see Excel VBA help for full details)
If you just want the address to be formatted, you'll then have to change the formatting of the other characters in the display text back to normalDim strLink As String

strLink = "http://blabla.com"

Worksheets(1).Hyperlinks.Add Anchor:=Worksheets(1).Range("A2"), _
Address:=strLink, _
TextToDisplay:="Read this link at " & strLink
With Worksheets(1).Range("A2").Characters(1, Len(Worksheets(1).Range("A2").Text) - Len(strLink)).Font
.Underline = xlUnderlineStyleNone
.ColorIndex = xlColorIndexAutomatic
End With

Might be nice to do this as a worksheet change and pick out the link


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1"
Dim iPos1 As Long
Dim iPos2 As Long
Dim sLink As String

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
iPos1 = InStr(LCase(.Value), "http://")
If iPos1 > 0 Then
iPos2 = InStr(iPos1 + 1, .Value, " ")
If iPos2 = 0 Then iPos2 = Len(.Value)
sLink = Mid(.Value, iPos1, iPos2 - iPos1 + 1)
.Hyperlinks.Add Anchor:=Target, _
Address:=sLink, _
TextToDisplay:=.Value
With .Characters(1, iPos1 - 1).Font
.Underline = xlUnderlineStyleNone
.ColorIndex = xlColorIndexAutomatic
End With
If iPos2 < Len(.Value) Then
With .Characters(iPos2 + 1, Len(.Value)).Font
.Underline = xlUnderlineStyleNone
.ColorIndex = xlColorIndexAutomatic
End With
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub