DoubleClick using Virtual Basic within Excel

fistral13

New Member
Joined
Dec 15, 2005
Messages
16
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?
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Ceduljko

Board Regular
Joined
Dec 28, 2004
Messages
58
Replace it with

ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, Address:= _
ActiveCell.Text, TextToDisplay:= ActiveCell.Text
 

fistral13

New Member
Joined
Dec 15, 2005
Messages
16
Hi Ceduljko,

Thanks for your prompt reply

After sending my question I restructured the Macro:

For I = 1 To 1000

Selection.Copy
Application.CutCopyMode = False
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=ActiveCell.Value
ActiveCell.Activate
ActiveCell.Offset(1, 0).Select

Next I

That coding proved to be successful.

We are pretty much on the same wavelength - Thanks again
 

Watch MrExcel Video

Forum statistics

Threads
1,118,821
Messages
5,574,515
Members
412,599
Latest member
Schu94
Top