Error using DataObject to get clipboard data

[stocktsi]

I'm using VBA to try to get the data on the clipboard in Excel. I've found
suggestion that are variations on the following:

Dim MyData As DataObject
Set MyData = New DataObject
Dim sClipText As String
On Error GoTo NotText
MyData.GetFromClipboard
sClipText = MyData.GetText(1)

When this sub gets called in my Excel macro, I get "User Defined Type not
defined" on the first line.

Also, once this is resolved, is there a way to determine if the data on the
clipboard can be pasted into Excel (sometimes you do something in Excel that
seems to clear out the clipboard).

Any suggestions?
Thanks.

 

[joel]

I've found the solution but iut puzzles me. Maybe somebody can explain it
better.

I found by adding a userform (Insert Userform) to the VBA project creates a
refernce (tools _ References) to the Microsoft forms 2.0 object library.
Once the object library is listed as a reference the Dataobject doesn't
create an error.

The thing that puzzle me is i can't just go and add the Userform refernce.
it is not in the libraries.

 

[Dave Peterson]

If you were stepping through the code, then you can't set a reference.

I'd try it again.

 

[joel]

Dave: i wasn't stepping through the code. You can't even select REFERENCE if
you are stepping. I created a new workbook with a one line macro.

Sub Test
Dim MyData As DataObject

end Sub


I compiled the code using debug Compile and got an error. Then went to
Refernce and looks for the Microsoft Forms 2.0 object librzary and couldn't
find it. Next I added a userform and went back to the REFERENCE menu and the
form was listed and checked. I then compiled and didn't get the error.

 

[Dave Peterson]

I don't have another guess.

If you close excel and reopen (with an empty workbook) and then straight to the
VBE, can you set the reference then?

(Maybe you just missed it in the reference list. It shows up near the top for
me (xl2003). Maybe you looked in the alphabetical list below???)