While I was looking for help coding a VBA routine to simply copy text from a message box to the Windows clipboard, I found many websites and forums offering ideas. Two methods were prominent and are briefly presented in this post. One of them worked for me.

Other the years, like many other VBA programmers, I visited Chip Pearson’s website for help. It was like the missing manual for VBA, with thousands of lines of code offered freely. It was truly sad to lose this man at such an early age, however, his well respected website thankfully lives on. I found the ultimate solution to my clipboard problem on this page at www.cpearson.com.

While some solutions required numerous Windows API calls, the two procedures which I most often found that send text to the clipboard are simple. The persons who offered the solutions said that they worked for them. I cannot argue that point, but the only one which has worked consistently for me is presented in Method 2.

Method 1 – Use a “htmlfile” object to send the text to the clipboard

This technique involves creating an ActiveX object of the type htmlfile using the CreateObject function. Then it uses the new object to send the text to the Windows clipboard. It really seems simple. However, there was different code shown to achieve this. Some used Early binding, some used Late binding, and some were one liners. The following is one example that didn’t work for me and the error message it produced.

Dim varKey as Variant
' Subtract the Application ID number from the Hash key
varKey = (13092054 - CLng(Sheet1.Range("V5").value))

With CreateObject("htmlfile")
    With .parentWindow.clipboardData
         ' Write to the clipboard
         .setData "text", varKey
    End With
End With

I used the various forms of this code presented to make it work, but it did not. This may or may not have worked for me at one time, or under another version of Excel or Windows (I am currently writing my VBA under Excel 365 and Windows 11.) The code looks familiar, and I am sure that I have used it. Some sources also said to add the Microsoft HTML Object Library (mshtml.tlb) reference to the VBA project to run it successfully. This did nothing for my code.

The htmlfile method was also demonstrated in a one line statement which creates the required object and uses it to send the variable to the clipboard. It is more concise and probably the one I would have used if it worked for me. Even with the HTML Object Library added, the result was an Automation error.

CreateObject("htmlfile").parentWindow.clipboardData.setData "text", varText

This post is about copying text reliably, so the next method I consider the best solution. It just worked.

Method 2 – Use the MSForms DataObject

I wanted a simple, concise bit of code which worked. I didn’t want to add another library to my project. The solution that worked only required the Microsoft Forms 2.0 Object Library (FM20.dll). I usually have this library added to my projects because they almost always include forms and controls. A screenshot of the References dialog for my BidSmart Estimator shows the libraries included in that VBA project.

Most of the solutions I looked over using this method were the same. They used the object declaration, “Dim DataObj As New DataObject” (Late binding), or used 2 lines, one for the Dim and the other to Set the declared object to a New Object, or New DataObject. The code from Chip Pearson’s website used Early binding and was essentially as shown in the following:

Dim varText As Variant	' Use a Variant for 64 bit compatibility
Dim DataObj As New MSForms.DataObject

' Set varText to some value
varText="Some value"

	' Send the text stored in the variale to the clipboard
	DataObj.SetText varText
	DataObj.PutInClipboard

That’s it, basically 3 lines of code excluding the setting of the variable’s value. And, it has worked for me in every situation to date. Simply changing the declaration statement from…

• Dim DataObj As New DataObject (or New Object)…to
• Dim DataObj As New MSForms.DataObject

…made all the difference. Changing the Dim to refer specifically to the MSForms DataObject made it work. The following is the entire Sub routine in which I used the Early binding code (included purely for context.)

Private Sub cmdHashKey_Click()
Dim varKey As Variant
Dim objData As New MSForms.DataObject

    ' Subtract the Application ID number from the Hash key
    varKey = (13092054 - CLng(Sheet1.Range("V5").value))

    If MsgBox("The license key for this App ID (" & Sheet1.Range("V5").value & ") is " _ 
	& varKey & vbCrLf & vbCrLf & "Click OK to copy it to the clipboard, or Cancel to close this message box", _
	vbOKCancel + vbDefaultButton2, "License key...") = vbOK Then
    
        objData .SetText varKey
        objData .PutInClipboard
	End If
	
End Sub

Thanks, Chip!