CODEWITHSUNDEEP
vbaexcel
ParthShah
ParthShah

Reputation: 25

Add text in clipboard as hyperlink to a cell

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".

Upvotes: 0

Views: 954

Answers (1)

Zwo
Zwo

Reputation: 1113

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

Upvotes: 1

Related Questions