I found this thread to a question relating to converting text URLs to clickable hyperlinks with VBA and I was wondering whether there is any way to use the following code to change any url to a clickable link whilst also changing the new hyperlink text to "Link":

Sub MakeSelectedHyperlinksHot()
    For Each Cell In Selection
    ActiveSheet.Hyperlinks.Add Anchor:=Cell, Address:=Cell.Value, TextToDisplay:=Cell.Value
    Next Cell
End Sub

I've tried the following change to the "TextToDisplay" field but a Run time error '5' occurs with "Invalid procedure call or argument" as the error message:

Sub HyperAdd()

    'Converts each text hyperlink selected into a working hyperlink

    For Each xCell In Selection
        ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula, TextToDisplay:=xLink
    Next xCell

End Sub

The original code works as it should but it'd be great to be able to customise the resulting text that's displayed.

Thanks very much.