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?

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

Sub add_hyperlink()

Dim hl As String
hl = InputBox("Copy link he")
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

--
Regards,
Anant


"jimmulv3" wrote:

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?

Sub add_hyperlink()

Dim hl As String
hl = InputBox("Copy link he")
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...
--
Regards,
Anant


"Anant Basant" wrote:

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

Sub add_hyperlink()

Dim hl As String
hl = InputBox("Copy link he")
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

--
Regards,
Anant


"jimmulv3" wrote:

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?

First, Chip Pearson explains how to get text off the clipboard he
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).

jimmulv3 wrote:

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?

--

Dave Peterson