excelvbahyperlinkformula

Mass Editing Hyperlinks in Excel

I am making a Contact Excel Sheet I am using for work and I am storing peoples LinkedIn Profiles in this document.

I am trying to keep the displayed size of the the links down so I am at presetn entering links via the Hyperlink Function and then changing the text displayed as 'Profile'

This is tedious, is there a formula or VBA to basically take a hyperlink in column K and change the display to 'Profile'?

I am using Excel 2013.

Solution

  • This will update the Text To Display in each hyperlink on Sheet1.

    Public Sub UpdateTextToDisplay()

    Dim HL As Hyperlink
    
    For Each HL In ThisWorkbook.Worksheets("Sheet1").Hyperlinks
        HL.TextToDisplay = "Profile"
    Next HL

End Sub

    Use this code to update the hyperlinks in a single column (I think you can use Columns("K") instead of Columns(11) if you prefer):

    Public Sub UpdateTextToDisplay()

    Dim HL As Hyperlink
    
    For Each HL In ThisWorkbook.Worksheets("Sheet1").Columns(11).Hyperlinks
        HL.TextToDisplay = "Profile"
    Next HL

End Sub

    Edit Hyperlink dialog box