Prev: Pop up message asks if I really want to edit the live office data
Next: Tweak to display autoshape
From: jimmulv3 on 28 Apr 2010 19:43
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?
From: Anant Basant on 28 Apr 2010 20:20
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

--
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?
From: Anant Basant on 28 Apr 2010 20:23
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...
--
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 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
>
> --
> 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?
From: Dave Peterson on 28 Apr 2010 20:42
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).

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
 | 
Pages: 1
Prev: Pop up message asks if I really want to edit the live office data
Next: Tweak to display autoshape