Jay Latimer,

I've included some code below for you to evaluate. I would consider
redesigning the InputBox for your column because you currently have no way of
testing that the user input is valid. Once you add the validation for the
first InputBox (i.e. strCol), you can get rid of the On Error statement. I
added a worksheet validation via setting the worksheet to a worksheet object.
Lastly, I used the Hyperlinks object (which works for inserted hyperlinks
and NOT for the HYPERLINK function) to loop through the hyperlink with the
range. I hope this helps.

Best,

Matthew Herbert

Sub ChangeLink()
Dim strCol As String
Dim strSht As String
Dim Wks As Worksheet
Dim Rng As Range
Dim lngLastRow As Long
Dim HLinks As Hyperlinks
Dim HLink As Hyperlink

On Error Resume Next
'maybe consider using Application.InputBox with a Type:=8, which
' will return a cell reference as a Range object
' (see Application.InputBox in VBE Help). This will
' make some of the validation easier, such as are there too
' many columns, did the user enter a number, etc.
strCol = InputBox("Enter column LETTER(S)")

strSht = InputBox("Enter sheet name")

'test if you have a valid worksheet
Set Wks = Worksheets(strSht)
If Wks Is Nothing Then Exit Sub

With Wks
lngLastRow = .Cells(.Rows.Count, strCol).End(xlUp).Row
Set Rng = Range(.Cells(2, strCol), .Cells(lngLastRow, strCol))
End With

'get a collection of hyperlinks within Rng
Set HLinks = Rng.Hyperlinks

'if a hyperlinks collection is returned, loop through each
' hyperlink and change the TextToDisplay property
If Not HLinks Is Nothing Then
For Each HLink In HLinks
HLink.TextToDisplay = "Tax record"
Next HLink
End If

End Sub

"JayLatimer" wrote:

I have a macro which converts urls in a column to the words "Tax Record" but
the macro will not work. When I run the Macro it prompts for Column and then
prompts for sheet name as expected. When valid inputis given to both
questions the Macro ends without error but does not convert the urls in the
column identified into the words "Tax Record"

I would appreicate any help I can get. The Marco is:

Sub chngelink()
'
' chngelink Macro
'
' Keyboard Shortcut: Ctrl+z
Dim MyCol As String
Dim MySht As String
On Error Resume Next
MyCol = InputBox("Enter column LETTER(S)")
MySht = InputBox("Enter sheet name")
Lastrow = Sheets(MySht).Cells(Cells.Rows.Count, MyCol).End(xlUp).Row
For Each c In Sheets(MySht).Range(MyCol & "2:" & MyCol & Lastrow)
c.Hyperlinks(1).TextToDisplay = "Tax record"
Next
End Sub