Excel VBA code to copy a specific string to clipboard

Question

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 1

EDIT - MSForms is deprecated, so you should no longer use my answer. Instead use this answer: https://stackoverflow.com/a/60896244/692098

I leave my original answer here only for reference:

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

Answer 2

To write text to (or read text from) the Windows clipboard use this VBA function:

Function Clipboard$(Optional s$)
    Dim v: v = s  'Cast to variant for 64-bit VBA support
    With CreateObject("htmlfile")
    With .parentWindow.clipboardData
        Select Case True
            Case Len(s): .setData "text", v
            Case Else:   Clipboard = .getData("text")
        End Select
    End With
    End With
End Function

---

'Three examples of copying text to the clipboard:
Clipboard "Excel Hero was here."
Clipboard var1 & vbLF & var2
Clipboard 123

'To read text from the clipboard:
MsgBox Clipboard

This is a solution that does NOT use MS Forms nor the Win32 API. Instead it uses the Microsoft HTML Object Library which is fast and ubiquitous and NOT deprecated by Microsoft like MS Forms. And this solution respects line feeds. This solution also works from 64-bit Office. Finally, this solution allows both writing to and reading from the Windows clipboard. No other solution on this page has these benefits.

Answer 3

The simplest (Non Win32) way is to add a UserForm to your VBA project (if you don't already have one) or alternatively add a reference to Microsoft Forms 2 Object Library, then from a sheet/module you can simply:

With New MSForms.DataObject
    .SetText "http://zombo.com"
    .PutInClipboard
End With

Answer 4

If the url is in a cell in your workbook, you can simply copy the value from that cell:

Private Sub CommandButton1_Click()
    Sheets("Sheet1").Range("A1").Copy
End Sub

(Add a button by using the developer tab. Customize the ribbon if it isn't visible.)

If the url isn't in the workbook, you can use the Windows API. The code that follows can be found here: http://support.microsoft.com/kb/210216

After you've added the API calls below, change the code behind the button to copy to the clipboard:

Private Sub CommandButton1_Click()
    ClipBoard_SetData ("http:\\stackoverflow.com")
End Sub

Add a new module to your workbook and paste in the following code:

Option Explicit

Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) _
   As Long
Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) _
   As Long
Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, _
   ByVal dwBytes As Long) As Long
Declare Function CloseClipboard Lib "User32" () As Long
Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) _
   As Long
Declare Function EmptyClipboard Lib "User32" () As Long
Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
   ByVal lpString2 As Any) As Long
Declare Function SetClipboardData Lib "User32" (ByVal wFormat _
   As Long, ByVal hMem As Long) As Long

Public Const GHND = &H42
Public Const CF_TEXT = 1
Public Const MAXSIZE = 4096

Function ClipBoard_SetData(MyString As String)
   Dim hGlobalMemory As Long, lpGlobalMemory As Long
   Dim hClipMemory As Long, X As Long

   ' Allocate moveable global memory.
   '-------------------------------------------
   hGlobalMemory = GlobalAlloc(GHND, Len(MyString) + 1)

   ' Lock the block to get a far pointer
   ' to this memory.
   lpGlobalMemory = GlobalLock(hGlobalMemory)

   ' Copy the string to this global memory.
   lpGlobalMemory = lstrcpy(lpGlobalMemory, MyString)

   ' Unlock the memory.
   If GlobalUnlock(hGlobalMemory) <> 0 Then
      MsgBox "Could not unlock memory location. Copy aborted."
      GoTo OutOfHere2
   End If

   ' Open the Clipboard to copy data to.
   If OpenClipboard(0&) = 0 Then
      MsgBox "Could not open the Clipboard. Copy aborted."
      Exit Function
   End If

   ' Clear the Clipboard.
   X = EmptyClipboard()

   ' Copy the data to the Clipboard.
   hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)

OutOfHere2:

   If CloseClipboard() = 0 Then
      MsgBox "Could not close Clipboard."
   End If

End Function

Answer 5

Add a reference to the Microsoft Forms 2.0 Object Library and try this code. It only works with text, not with other data types.

Dim DataObj As New MSForms.DataObject

'Put a string in the clipboard
DataObj.SetText "Hello!"
DataObj.PutInClipboard

'Get a string from the clipboard
DataObj.GetFromClipboard
Debug.Print DataObj.GetText

Here you can find more details about how to use the clipboard with VBA.

Answer 6

If you want to put a variable's value in the clipboard using the Immediate window, you can use this single line to easily put a breakpoint in your code:

Set MSForms_DataObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}"): MSForms_DataObject.SetText VARIABLENAME: MSForms_DataObject.PutInClipboard: Set MSForms_DataObject = Nothing

Answer 7

If the place you're gonna paste have no problem with pasting a table formating (like the browser URL bar), I think the easiest way is this:

Sheets(1).Range("A1000").Value = string
Sheets(1).Range("A1000").Copy
MsgBox "Paste before closing this dialog."
Sheets(1).Range("A1000").Value = ""

Answer 8

I tested this code in excel 365 and it worked

Dim str as String
str = "Hello Copied"
Windows.Parent.Clipboard str

Note: I created the variable because the code does not process string concatenation

Answer 9

The code given at the Microsoft site works in Excel too, even though it is under Access VBA. I tried it in Excel 365 on a 64 bit Windows 10.

Microsoft Site Link: https://learn.microsoft.com/en-us/office/vba/access/Concepts/Windows-API/send-information-to-the-clipboard

Copying here for answer completeness.

Option Explicit
Private Declare Function OpenClipboard Lib "user32.dll" (ByVal hWnd As Long) As Long
Private Declare Function EmptyClipboard Lib "user32.dll" () As Long
Private Declare Function CloseClipboard Lib "user32.dll" () As Long
Private Declare Function IsClipboardFormatAvailable Lib "user32.dll" (ByVal wFormat As Long) As Long
Private Declare Function GetClipboardData Lib "user32.dll" (ByVal wFormat As Long) As Long
Private Declare Function SetClipboardData Lib "user32.dll" (ByVal wFormat As Long, ByVal hMem As Long) As Long
Private Declare Function GlobalAlloc Lib "kernel32.dll" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
Private Declare Function GlobalLock Lib "kernel32.dll" (ByVal hMem As Long) As Long
Private Declare Function GlobalUnlock Lib "kernel32.dll" (ByVal hMem As Long) As Long
Private Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function lstrcpy Lib "kernel32.dll" Alias "lstrcpyW" (ByVal lpString1 As Long, ByVal lpString2 As Long) As Long

Public Sub SetClipboard(sUniText As String)
    Dim iStrPtr As Long
    Dim iLen As Long
    Dim iLock As Long
    Const GMEM_MOVEABLE As Long = &H2
    Const GMEM_ZEROINIT As Long = &H40
    Const CF_UNICODETEXT As Long = &HD
    OpenClipboard 0&
    EmptyClipboard
    iLen = LenB(sUniText) + 2&
    iStrPtr = GlobalAlloc(GMEM_MOVEABLE Or GMEM_ZEROINIT, iLen)
    iLock = GlobalLock(iStrPtr)
    lstrcpy iLock, StrPtr(sUniText)
    GlobalUnlock iStrPtr
    SetClipboardData CF_UNICODETEXT, iStrPtr
    CloseClipboard
End Sub

Public Function GetClipboard() As String
    Dim iStrPtr As Long
    Dim iLen As Long
    Dim iLock As Long
    Dim sUniText As String
    Const CF_UNICODETEXT As Long = 13&
    OpenClipboard 0&
    If IsClipboardFormatAvailable(CF_UNICODETEXT) Then
        iStrPtr = GetClipboardData(CF_UNICODETEXT)
        If iStrPtr Then
            iLock = GlobalLock(iStrPtr)
            iLen = GlobalSize(iStrPtr)
            sUniText = String$(iLen \ 2& - 1&, vbNullChar)
            lstrcpy StrPtr(sUniText), iLock
            GlobalUnlock iStrPtr
        End If
        GetClipboard = sUniText
    End If
    CloseClipboard
End Function

The above code can be called from a Custom Macro as follows:

Sub TestClipboard()
    Dim Val1 As String: Val1 = "Hello Clipboard " & vbLf & "World!"
    SetClipboard Val1
    MsgBox GetClipboard
End Sub

To Show a button on a form, you can find a good example by a quick serach. To show a button in the Excel Custom Ribbon (One that shows only in the current Excel workbook) you can use CustomUI.

CustomUI links:

https://bettersolutions.com/vba/ribbon/custom-ui-editor.htm

https://learn.microsoft.com/en-us/office/open-xml/how-to-add-custom-ui-to-a-spreadsheet-document

imageMSO List with Icons (Used in CustomUI):

https://bert-toolkit.com/imagemso-list.html

Thanks.