CODEWITHSUNDEEP
vbaexcel
Emir
Emir

Reputation: 481

VBA macro -- hyperlink

I'd like to create a VBA macro which would allow me to edit all selected hyperlinks in a column and change "text to display" to the same word for all. For example, if this was the column:

www.google.com/search=cars
www.google.com/search=houses
www.google.com/search=cities

I would want to highlight those three elements of the column and change the text to display to "Google Search" so that the outcome would be this:

Google Search
Google Search
Google Search

Edit: So I found a macro similar to what I want to do on the microsoft support site, but my issue is that this macro targets all the hyperlinks in the sheet while I'd want to select a specific column to edit the hyperlinks.

Sub HyperLinkChange()
   Dim oldtext As String
   Dim newtext As String
   Dim h As Hyperlink


 oldtext = "http://www.microsoft.com/" 
 newtext = "http://www.msn.com/" 

   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

Upvotes: 1

Views: 2465

Answers (1)

DJ.
DJ.

Reputation: 16247

This works on the current selection:

Sub SetLinkText()

Dim LinkText As String
Dim h As Hyperlink

    LinkText = InputBox("Enter link text")

    If LinkText = "" Then Exit Sub

    For Each h In Selection.Hyperlinks
        h.TextToDisplay = LinkText
    Next

End Sub

Upvotes: 1

Related Questions