Macro for Word or Excel that changes hyperlink display text to address

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

susan.fineman@verizon.net - 01 Apr 2006 16:14 GMT

I have over 1000 citations for which the article titles are the display
text for the hyperlinks. For each article I want to change the display
text to the hyperlink address. I tried recording a new macro, but it
wouldn't record the right-mouse "edit hyperlink" selection. I couldn't
find a keystroke alternative for selecting edit hyperlink. I am new to
writing macros in VBA but different postings imply that this is the way
to automate my task. Can someone please tell me how to write a macro
to accomplish this task?

Reply to this Message

Helmut Weber - 01 Apr 2006 23:00 GMT

Hi Susan,

maybe the limitations of this aren't relevant for your:

Dim oHpl As Hyperlink
For Each oHpl In ActiveDocument.Hyperlinks
oHpl.TextToDisplay = oHpl.Address
Next

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

Reply to this Message

StephanieH - 26 May 2006 20:36 GMT

This is one that I use a lot. You can alter it for your needs.

Sub HyperChangePlot()

Dim oldtext As String
Dim newtext As String
Dim h As Hyperlink

oldtext = "Gross Plotting by Product"
newtext = "Gross Placement Plotting"

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

> Hi Susan,
>
[quoted text clipped - 4 lines]
> oHpl.TextToDisplay = oHpl.Address
> Next

Reply to this Message