Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperlinks: Hyperlinks change on copy/paste? | Excel Worksheet Functions | |||
Update 2000 Excel hyperlinks to 2003 hyperlinks | Excel Worksheet Functions | |||
How toi turn-off hyperlinks [excel]? Email hyperlinks pop up ! | Excel Discussion (Misc queries) | |||
Excel Hyperlinks- cell content v. hyperlinks | Excel Discussion (Misc queries) | |||
Hyperlinks to =Hyperlinks formula - Challenging | Excel Programming |