Change hyperlink 'text to display' to match 'hyperlink adress' using VBA

Dear Experts:

I got hundreds of hyperlinks on the active worksheet.

I would like to change all hyperlinks' property 'Text to display' to exactly match the property 'Hyperlink address' using VBA.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
Andreas HermleTeam leaderAsked:
Who is Participating?
 
terencinoConnect With a Mentor Commented:
In short..
Sub FixLinks()
For Each hyp In ActiveSheet.Hyperlinks
    hyp.TextToDisplay = hyp.Address
Next hyp
End Sub

Open in new window

...Terry
0
 
BenefordConnect With a Mentor Commented:
This'll do it.  (And you could add some code to strip off the http:// or mailto: at the beginning of each URL if you don't want them included).

Sub FixHyperlinks()

'  Replace all text showing behind hyperlinks with the hyperlink itself

    Dim aSheet As Worksheet
    Dim aCell As Range
'
    For Each aSheet In Sheets
        For Each aCell In aSheet.UsedRange.Cells
            If aCell.Hyperlinks.Count > 0 Then
               aCell = aCell.Hyperlinks.Item(1).Address
            End If
        Next
    Next
    

Open in new window

0
 
stergiumConnect With a Mentor Commented:
Hello
You need something like this ?

Sub HyperLinkChange()
   Dim oldtext As String
   Dim newtext As String
   Dim h As Hyperlink
       oldtext = "previous value"
       newtext = "Text to display"

' Check all hyperlinks on active sheet.
       For Each h In ActiveSheet.Hyperlinks
       x = InStr(1, h.Address, oldtext)
       If x > 0 Then
           If h.TextToDisplay = h.Address Then
                h.TextToDisplay = newtext
           End If
           h.Address = Application.WorksheetFunction. _
           Substitute(h.Address, oldtext, newtext)
       End If
       Next
End Sub

hope that helps
0
 
Andreas HermleTeam leaderAuthor Commented:
Dear all,

thank you very much  for your overwhelming support. I will test it tomorrow and then get back to you.  

Again, thank you very much.

Regards, Andreas
0
 
Andreas HermleTeam leaderAuthor Commented:
Dear all,

thank you very much for your overwhelming support. All of the codes work just fine. Terencino's one is the most concise therefore the most points go to him.

Again, thank you very much for your professional and valuable help.

Regards, Andreas
0
All Courses

From novice to tech pro — start learning today.