Hyperlinks
 

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

Hyperlinks
 

Pam,
I don't think so...but assuming it's written as
www.alabamastatecommision.com you 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

Hyperlinks
 

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

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

Hyperlinks
 

Doug,

It worked gret thanks!

Pat

On Nov 21, 4:36 pm, "Doug Glancy" wrote:
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 ooglegroups.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 towww.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.comyoucould
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