How to recall a path from clipboard for a macro to use

[makhtoot]

Hello Dear Friends
I have a very simple Macro Code but want to alter a part of it. i want instead of the line in front of the Address, paste the path from the clipboard. the code is like this:

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+d
'
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="D:\Test\0157.jpg"
End Sub

Thank you in advance

 

[Jaafar Tribak]

Here is a variation of PGC's code but without the need to set a reference to the MS Forms2.0 library at design time - See if it now works for you.

Sub test()

    Dim DObj As Object
    Dim s As String
    
    Set DObj = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    ' Get the text in the clipboard into s
    DObj.GetFromClipboard
    s = DObj.GetText
    Set DObj = Nothing
    ' add the hyperlink
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=s

End Sub

 

[pgc01]

Hi Jaafar

Nice! Did not know how to do it without setting the reference.


Hi makhtoot

I thought that your problem was to know how to get the text in the clipboard, which you now know how to do (in 2 ways). It seems, though, that there's something else you need?

 

[makhtoot]

thank you my friends
it did exactly what i asked for but. but one other question, how can i assign a shortcut key to this one?
thank you again for all the kind help

 

[pgc01]

Hi

To assign a shortcut check the help on the OnKey method of the Application object.

 

[makhtoot]

thank you my friend