VBA to Paste a HYPERLINK to a cell without changing the cell content.

This sounded impossible at first

Then I thought perhaps you could use a comment.

To add a clickable hyperlink to comment, just do as follow steps:

1. Right click at the cell you want to add hyperlink to its comment, and select Insert Comment from context menu.

2. Then in the comment box, type the hyperlink address into it, and then remove other characters including the author name.

3. Now you need to press Alt + End keys to make sure there are no other characters (including blank) in the comment box.

4. Right click at the comment cell, and select Show/Hide Comments to show the comment.

I used http://www.cnn.com and clicking in the comment box took me to the site.

If you do not want the comment open all the time then I suppose you could either:-

Use a macro to show the comment when you click on a cell
Use a macro to open the hyperlink when you click on the cell
Use a macro to swap the activecell contents with the contents of the comment


This snippet worked.

Please Login or Register  to view this content.

This also worked

Please Login or Register  to view this content.

Hello
Try this code .. Suppose you have range("A1:A" & lr) with hyperlinks and need to copy the hyper links only on the cells in column B

Please Login or Register  to view this content.