How to read or access the clipboard with Excel VBA?

You can access and retrieve the data in the clipboard into a variable in Excel VBA, using the Microsoft Forms library. On top of copy and paste the code below, you must follow the these steps and activate Microsoft Forms 2.0 Object Library for the code to work. You only need to do it once as long as this macro stays in the same file.

  1. On the Macro editor menu bar on the top, click on “Tools”
  2. In the menu, click on “References”
  3. Click the “Microsoft Forms 2.0 Object Library”
  4. Press OK
If you don’t follow these steps, you will get a “Compile error: User-defined type not defined” error… Lots of people have been stumped on this part.

Public Clipboard As New MSForms.DataObject

Sub readClipboard()
'Tools -> References -> Microsoft Forms 2.0 Object Library
'of you will get a "Compile error: user-defined type not defined"
  Dim DataObj As New MSForms.DataObject
  Dim S As String
  DataObj.GetFromClipboard
  S = DataObj.GetText
  Debug.Print S 'print code in the Intermediate box in the Macro editor
End Sub

 

 

2 thoughts on “How to read or access the clipboard with Excel VBA?

  1. Blackie Hamel says:

    Despite all the warnings, I still get “Compile error: user-defined type not defined.” When I go to Tools | References, I have the choice of “Microsoft Forms 12.0 Object Library” and not version 2.0. Does that explain why this error arises?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>