How do I create a hyperlink with a macro?

[jimmulv3]

Hello,

I am attempting to create a spread sheet for work that is as easy to use as
possible. My goal is to create a macro that adds a hyperlink to a particular
cell. The catch is, I would like the URL to come from the clipboard. This way
all they need to do is copy the address and run the macro. Is this possible?

 

[Anant Basant]

Hope this helps... You can paste link copied from clipboard into an inputbox...

Sub add_hyperlink()

Dim hl As String
hl = InputBox("Copy link here:")
If Len(hl) = 0 Then Exit Sub
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"http://www.microsoft.com", ScreenTip:="Goto Microsoft's site", _
TextToDisplay:="Microsoft"
End Sub

 

[Anant Basant]

Sub add_hyperlink()

Dim hl As String
hl = InputBox("Copy link here:")
If Len(hl) = 0 Then Exit Sub
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
hl, ScreenTip:="Goto Microsoft's site", _
TextToDisplay:="Microsoft"
End Sub

sorry!! forgot to replace variable in the last post...

 

[Dave Peterson]

First, Chip Pearson explains how to get text off the clipboard here:
http://www.cpearson.com/excel/Clipboard.aspx

Read his notes carefully. There's a warning about setting a reference to
Microsoft Forms 2.0 Object Library that you need to do.

This modified version of his sample code worked ok for me:

Option Explicit
Sub testme()

Dim DataObj As MSForms.DataObject
Dim myStr As String
Dim myCell As Range

Set DataObj = New MSForms.DataObject

DataObj.GetFromClipboard
myStr = DataObj.GetText

'check for a leading HTTP:
If UCase(Left(myStr, 5)) = UCase("http:") Then
'some cell
Set myCell = ActiveSheet.Range("A3")

'pesky spaces???
myStr = Replace(myStr, " ", "%20")

'=hyperlink() formula style of hyperlink
myCell.Formula = "=hyperlink(""" & myStr & """,""click me"")"

'or Insert|Hyperlink style
myCell.Hyperlinks.Add anchor:=myCell, _
Address:=myStr, TextToDisplay:=myStr

End If

End Sub

========
You can have two different styles of hyperlinks in excel -- the =hyperlink()
version and the Insert|Hyperlink (ctrl-k) version.

Personally, I find the =hyperlink() worksheet formula much nicer behaved.

But don't use both. Delete one of them from the code (or comment it out).