Why will this macro only run from PERSONAL.xlsb?

I finally got this macro working. With the full filename of a JPG image on the clipboard, it insers that as a picture in the active worksheet:

Please Login or Register  to view this content.

HOWEVER, it will only work if I place it in Personal.xlsb. If I put it in the worksheet in which I want to work, TrackData-New.xlsm, I get this error: "Compile error: User-defined type not defined".

I did observe the obscure instruction I had found, namely that for this to work you must "Have at least one UserForm in your project".

ClipMacroNotWorking.jpg

Anyone know the reason for this failure and how to fix please?

User forms are not the same things as user-defined data types, so I'm not sure why the user form should be a part of this. User-defined data types are defined using the Type statement (https://msdn.microsoft.com/en-us/vba...type-statement ). You use the Dim statement to define a variable as a built in or user-defined data type (https://msdn.microsoft.com/en-us/vba.../dim-statement shows a list of built in data types).

Your insertpicture procedure dim's a variable MyData as a DataObject type -- which does not look like one of VBA's built in data types, so I think this is what it is looking for when it says there is no user-defined type defined. I am guessing that your personal.xlsb has a Type statement somewhere that defines what a DataObject type contains. When the code is contained in the same module as the Type statement that defines this user-defined type, the code works just fine.

The first solution I see is to make sure that you copy the Type definition into the desired VBA project module along with the Sub code. The other possibility is to restructure the code so that it does not use the user-defined type.

Did you make a reference to Microsoft Forms 2.0 Object Library in the workbook you're working in ?
Also not necessary to declare DataObject.

Please Login or Register  to view this content.

You can skip the reference and late bind

Please Login or Register  to view this content.

You should also be aware that the dataobject is a little buggy in versions of Windows from 8 onwards.

Or even.

Please Login or Register  to view this content.

Originally Posted by bakerman2

Did you make a reference to Microsoft Forms 2.0 Object Library in the workbook you're working in ?
Also not necessary to declare DataObject.

Please Login or Register  to view this content.

Thanks, a breakthrough! No, I had not made "...a reference to Microsoft Forms 2.0 Object Library...". (And have no idea why I would need to. Or why it isn't set as a default if stuff needs it. I need to start prefacing my questions with 'VBA novice'.) But having now done so 'my' macro now works in the target worksheet as well as Personal.xlsb.

However yours gives an error:

Please Login or Register  to view this content.

Having been asked recently not to edit posts under discussion, I'm now replying to posts individually. So maybe the later posts from you and xlnitwit will explain that.

Originally Posted by xlnitwit

You can skip the reference and late bind

Please Login or Register  to view this content.

You should also be aware that the dataobject is a little buggy in versions of Windows from 8 onwards.

Thanks, no idea why but that works a treat!

Please Login or Register  to view this content.

Originally Posted by bakerman2

Or even.

Please Login or Register  to view this content.

Yes, that works sweetly too , many thanks!

Please Login or Register  to view this content.

Thanks MrShorty.

As a VBA novice (and likely to remain one) I'm afraid most of that is over my head. And the more I dug into it (early binding, late binding, type libraries, application references, object declarations, etc) the more lost I became.

But I latched onto your suspicion: "I am guessing that your personal.xlsb has a Type statement somewhere that defines what a DataObject type contains." As you'll have seen from my last few replies, I have it working now (in any of three forms). But I'm still curious as to why it worked before in PERSONAL.xlsb, before I had seen bakerman2's point about a "reference to Microsoft Forms 2.0 Object Library". So I've just searched the PERSONAL 'project' using the target 'Type'. From its scores of macros, only this one below gave a hit. (Can't remember its source.) Do you think this could be the answer to the puzzle?

Please Login or Register  to view this content.

You must, at some point, have added a reference to the MSForms library. Adding a userform would automatically have done that for you.

Most important thing is that you got it all working smoothly.
Thanks for rep+.