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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.