Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro That doesn't work - Please help
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro That doesn't work - Please help
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro That doesn't work - Please help
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 "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to sum doesn't work | Excel Discussion (Misc queries) | |||
Macro to update a column in a work based on another work sheet | New Users to Excel | |||
Macro works Macro does not work | Excel Discussion (Misc queries) | |||
If I have a work sheet protected and try to run a macro to hide rows or columns it won't work. Correct? | Excel Programming | |||
Why does the macro not work? | Excel Worksheet Functions |