How do you copy the contents of a Cell into the Clipboard

Question

I'm trying to copy the cell contents into the clipboard.

I've read and tried the exact example provided in the Excel 2007 Help file. However for some reason the DataObject object is not valid. So the example:

Dim MyData As DataObject

Private Sub CommandButton1_Click()
    Set MyData = New DataObject

    MyData.SetText TextBox1.Text
    MyData.PutInClipboard

    TextBox2.Paste
End Sub

Private Sub UserForm_Initialize()
    TextBox1.Text = "Move this data to a " _
        & "DataObject, to the Clipboard, then to " _
        & "TextBox2!"
End Sub

Does not work in my case. I've searched for a good while now and I can not find an answer to why the DataObject object is not available.

Here is my code:

Dim MyData As DataObject

Private Sub Worksheet_Change(ByVal Target As Range)
    If ActiveCell.Column = 3 Then
        Set MyData = New DataObject
        MyData.SetText ActiveCell.Offset(-1, -1).Text
        MyData.PutInclipboard
    End If    
End Sub

Error on Compile is: "User-Defined type not defined" and it highlights the "MyData As DataObject" line.

Is there another method to simply copying the text in a cell to the clipboard?

Answer 1

Ok, few things:

First you need to Add a reference to "Microsoft Forms 2.0 Object Library", if you don't feel like searching through, just add a user form to the project and then immediately delete it, the reference will stay. You need that reference to use DataObject

I'll attach some code that I got to work, I changed the offsets but make them whatever you need...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Set MyData = New DataObject
    If ActiveCell.Column = 3 Then
        MyData.SetText ActiveCell.Offset(-1, 0).Text
        MyData.PutInClipboard
    End If
End Sub