Excel VBA code to copy a specific string to clipboard

excel clipboard vba
user1958738 picture user1958738 · Jan 8, 2013 · Viewed 165.5k times · Source

I'm trying to add a button to a spreadsheet that when clicked will copy a specific URL to my clipboard.

I had a bit of knowledge of Excel VBA but it's been a while and I'm struggling.

Answer

Jroonk picture Jroonk · Aug 16, 2014

This macro uses late binding to copy text to the clipboard without requiring you to set references. You should be able to just paste and go:

Sub CopyText(Text As String)
    'VBA Macro using late binding to copy text to clipboard.
    'By Justin Kay, 8/15/2014
    Dim MSForms_DataObject As Object
    Set MSForms_DataObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    MSForms_DataObject.SetText Text
    MSForms_DataObject.PutInClipboard
    Set MSForms_DataObject = Nothing
End Sub

Usage:

Sub CopySelection()
    CopyText Selection.Text
End Sub

Related questions

Get text from clipboard using GetText - avoid error on empty clipboard

I'm using code like this to get text from off the Clipboard. Dim DataObj As New MSForms.DataObject DataObj.GetFromClipboard myString = DataObj.GetText I use error handling to get the past the case where the Clipboard is empty, and everything …

Read More
Copy email to the clipboard with Outlook VBA

How do I copy an email to the clipboard and then paste it into excel with the tables intact? I am using Outlook 2007 and I want to do the equivalent of "Click on email > Select All > Copy > …

Read More
Leave out quotes when copying from cell

Problem: When copying a cell from Excel outside of the program, double-quotes are added automatically. Details: I'm using Excel 2007 on a Windows 7 machine. If I have a cell with the following formula: ="1"&CHAR(9)&"SOME NOTES FOR LINE 1."&…

Read More