Posted to microsoft.public.excel.programming
|
|
Hyperlinks
Pam,
How about this?
Sub edit_link()
Dim hlink As Hyperlink
For Each hlink In ActiveSheet.Hyperlinks
hlink.TextToDisplay = hlink.Address
Next hlink
End Sub
hth,
Doug
wrote in message
oups.com...
Thanks for the response however, I am looking for something different.
I am saying that the Text Desplayed is "Alabama State Commission" but
when clicked takes you to www.alabamastatecommision.com. How can I
turn the "Alabama State Commission" into the hyperlink attached to it?
On Nov 21, 3:15 pm, "Matt" wrote:
Pam,
I don't think so...but assuming it's written
aswww.alabamastatecommision.comyou could
go in the opposite direction by removing the www. from the beginning
and the
.com from the end of each link and then normalizing the font. See below
Sub EditCells()
Dim wkb As Workbook
Dim wks As Worksheet
Dim h As Hyperlink
Set wkb = ThisWorkbook
Set wks = wkb.Worksheets("[Your Sheet Name]")
For Each h In wks.Hyperlinks
If Left(h.TextToDisplay, 4) = "www." Then
h.TextToDisplay = Mid(h.TextToDisplay, 5, Len(h.TextToDisplay))
End If
If Right(h.TextToDisplay, 4) = ".com" Then
h.TextToDisplay = Mid(h.TextToDisplay, 1, InStr(1, h.TextToDisplay,
".com") - 1)
End If
Next
For I = 1 To wks.UsedRange.Rows.Count
wks.Cells(I, 1).Select
With Selection.Font
.Name = "Courier New"
.FontStyle = "Regular"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Next
End Sub
MS
On Nov 21, 2:59 pm, wrote:
I am having a problem writing a code for the following situation,
please help.
I have a list of links ie. Alabama State Commission and there are
address associated with those links,www.alabamastatecommission.com.
Is there a way I could automatically turn the Alabama State Commission
text to the actual web address?
I can do it the long way but I may have to do this a bunch of times and
figured it would be easier to write a code.
Thanks for your help.
Pat
|