DoubleClick
Imagine a Microsoft Excel workbook in which one particular page has a column in which successive rows are occupied by various URL’s as text.
I wanted to convert each cell to be a HyperLink.
I could have done this manually for each active cell using:
Click right mouse button
Select “Copy”
Click right mouse button
Select “HyperLink”
Alt+T
Ctrl+C
Alt+E
Ctrl+V
Enter
Enter
By chance I found that for an active cell:
Double click left mouse button
Enter
had the effect of producing the required HyperLink with the URL as the text which was displayed, and moving down one row.
I could have repeated the double click and enter hundreds of times for each active cell, but that is very time consuming. So I tried to write a Macro to achieve the same effect as double click and enter.
When I used the path:
Tools > Macro > Record New Macro
repeated the keystrokes, and stopped recording, I found that the coding in the Macro was:
Range("H1930").Select
ActiveCell.FormulaR1C1 = "http://links.........?ID=602"
Range("H1931").Select
As this was not suitable for any active cell selected I tried the following coding:
ActiveCell.Select
Keystrokes:
Click right mouse button
Select “Copy”
Click right mouse button
Select “HyperLink”
Alt+T
Ctrl+C
Alt+E
Ctrl+V
Enter
Enter
The coding in my Macro then became:
(After I had added the lines in Dark Red colour.)
ActiveCell.Select
Selection.Copy
Application.CutCopyMode = False
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"http://links.....?ID=603", TextToDisplay:= _http://links.....ID=603
ActiveCell.Offset(1, 0).Select
The Macro did not work as required, as it repeated the previous URL.
My question is what should replace the coding coloured Violet in the above Macro?
Imagine a Microsoft Excel workbook in which one particular page has a column in which successive rows are occupied by various URL’s as text.
I wanted to convert each cell to be a HyperLink.
I could have done this manually for each active cell using:
Click right mouse button
Select “Copy”
Click right mouse button
Select “HyperLink”
Alt+T
Ctrl+C
Alt+E
Ctrl+V
Enter
Enter
By chance I found that for an active cell:
Double click left mouse button
Enter
had the effect of producing the required HyperLink with the URL as the text which was displayed, and moving down one row.
I could have repeated the double click and enter hundreds of times for each active cell, but that is very time consuming. So I tried to write a Macro to achieve the same effect as double click and enter.
When I used the path:
Tools > Macro > Record New Macro
repeated the keystrokes, and stopped recording, I found that the coding in the Macro was:
Range("H1930").Select
ActiveCell.FormulaR1C1 = "http://links.........?ID=602"
Range("H1931").Select
As this was not suitable for any active cell selected I tried the following coding:
ActiveCell.Select
Keystrokes:
Click right mouse button
Select “Copy”
Click right mouse button
Select “HyperLink”
Alt+T
Ctrl+C
Alt+E
Ctrl+V
Enter
Enter
The coding in my Macro then became:
(After I had added the lines in Dark Red colour.)
ActiveCell.Select
Selection.Copy
Application.CutCopyMode = False
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"http://links.....?ID=603", TextToDisplay:= _http://links.....ID=603
ActiveCell.Offset(1, 0).Select
The Macro did not work as required, as it repeated the previous URL.
My question is what should replace the coding coloured Violet in the above Macro?
