Add text in clipboard as hyperlink to a cell

Question

I want to create macro which when run, pastes the link contained in clipboard to the current cell as a hyperlink. I tried with the record macro the following code was generated which I modified a little:

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="abc.com" _
        , TextToDisplay:="Link"
End Sub

Here, instead of abc.com it should be something like "paste text in keyboard".

http://stackoverflow.com/questions/9022245/get-text-from-clipboard-using-gettext-avoid-error-on-empty-clipboard — DanL, Oct 12 '15 at 07:19

score 1 · Accepted Answer · answered Oct 12 '15 at 08:12

According to the @DanL comment, here is the code you need :

Sub Macro1()
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=GetClipboardText() _
        , TextToDisplay:="Link"
End Sub

Function GetClipBoardText() as String
   Dim DataObj As MSForms.DataObject
   Set DataObj = New MsForms.DataObject '<~~ Amended as per jp's suggestion

   On Error GoTo Whoa

   '~~> Get data from the clipboard.
   DataObj.GetFromClipboard

   '~~> Get clipboard contents
   GetClipBoardText = DataObj.GetText(1)


   Exit Sub
Whoa:
   If Err <> 0 Then MsgBox "Data on clipboard is not text or is empty"
End Sub

Add text in clipboard as hyperlink to a cell

1 Answers1