Macro That doesn't work - Please help

[JayLatimer]

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

 

[Matthew Herbert]

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

 

[Matthew Herbert]

Jay,

I forgot to mention Shortcut Keys in my last post. I would recommend that
you not use native Excel shortcuts as your custom macro shortcut because you
will likely override the native shortcut key when you do so. For example,
Ctrl+z is the shortcut key for Undo, and rather than getting Undo when you
use Ctrl+z, you'll get your macro.

There is a whole host of shortcut keys in Excel, but some common Ctrl+
shortcut keys are as follows: Ctrl+ z, x, c, v, b, n, a, s, d, f, g, h, w,
r, u, i, o, p, and 1.

Best,

Matthew Herbert

 

[Don Guillett]

try this

Option Explicit
Sub chngelinkSAS()
Dim lastrow As Long
Dim i As Long
Dim MyCol As String
Dim MySht As String
On Error Resume Next
MyCol = InputBox("Enter column LETTER(S)")
MySht = InputBox("Enter sheet name")
Sheets(MySht).Select
lastrow = Sheets(MySht) _
..Cells(Cells.Rows.Count, MyCol).End(xlUp).Row
For i = 2 To lastrow
ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, MyCol), _
Address:=Cells(i, MyCol).Hyperlinks(1).Address, _
TextToDisplay:="Tax Record"
Next i
End Sub