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:

Sub InsertPicture_Gf4()
'
Dim MyData As DataObject
Dim strClip As String

Set MyData = New DataObject
MyData.GetFromClipboard
strClip = MyData.GetText
Range("A30").Select
    ActiveSheet.Pictures.Insert(strClip).Select
    Selection.ShapeRange.ScaleHeight 0.85, msoFalse, msoScaleFromTopLeft
    Application.CommandBars("Format Object").Visible = False
    Rows("27:27").Select
End Sub

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.

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?

Sub CloseAllVBEWindows() 'CR v5207

'Closes all VBE windows except this one!
'Requires library 'Microsoft Visual Basic for Applications Extensibility'
'CR 02/02/2016 - added error handling to fix issue in 64-bit Office
'In My Tab

On Error GoTo Err_Handler

Dim vbWin As Object
Const vbext_wt_CodeWindow = 0
Const vbext_wt_Designer = 1

For Each vbWin In Application.VBE.Windows
     If (vbWin.Type = vbext_wt_CodeWindow Or _
         vbWin.Type = vbext_wt_Designer) And _
         Not vbWin Is Application.VBE.ActiveWindow Then
             vbWin.Close
     End If
 Next
 
Exit_Handler:
    Exit Sub

Err_Handler:
    If Err.Number = 424 Then Resume Next 'object required
    MsgBox "Error " & Err.Number & " in CloseAllVBEWindows procedure: " & Err.Description
    Resume Exit_Handler

End Sub

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.

Sub InsertPicture_Gf4()
'
Dim strClip As String

With New DataObject
    .GetFromClipboard
    strClip = .GetText
End With
Range("A30").Select
    ActiveSheet.Pictures.Insert(strClip).Select
    Selection.ShapeRange.ScaleHeight 0.85, msoFalse, msoScaleFromTopLeft
    Application.CommandBars("Format Object").Visible = False
    Rows("27:27").Select
End Sub

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.

Sub InsertPicture_Gf4()
'
Dim strClip As String

With New DataObject
    .GetFromClipboard
    strClip = .GetText
End With
Range("A30").Select
    ActiveSheet.Pictures.Insert(strClip).Select
    Selection.ShapeRange.ScaleHeight 0.85, msoFalse, msoScaleFromTopLeft
    Application.CommandBars("Format Object").Visible = False
    Rows("27:27").Select
End Sub

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:

Sub InsertPicture_Gf4_bakerman2_1()
' NOT WORKING. Error "Unable to get the Insert property of the Pictures class"
' Debug refers to the ActiveSheet line
Dim strClip As String

With New DataObject
    .GetFromClipboard
    strClip = .GetText
End With
Range("A30").Select
    ActiveSheet.Pictures.Insert(strClip).Select
    Selection.ShapeRange.ScaleHeight 0.85, msoFalse, msoScaleFromTopLeft
    Application.CommandBars("Format Object").Visible = False
    Rows("27:27").Select
End Sub

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.

You can skip the reference and late bind

   Dim MyData           As Object
   Set MyData = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

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

Originally Posted by xlnitwit

You can skip the reference and late bind

   Dim MyData           As Object
   Set MyData = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

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!

Sub InsertPicture_Gf4_xlnitwit()
' "You can skip the reference and late bind" - no idea what that means _
' but took a stab at editing accordingly
' WORKS. Maybe I'll find time later to learn WHY!
Dim MyData As Object 'Was "DataObject"
Dim strClip As String

Set MyData = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
MyData.GetFromClipboard
strClip = MyData.GetText
Range("A30").Select
    ActiveSheet.Pictures.Insert(strClip).Select
    Selection.ShapeRange.ScaleHeight 0.85, msoFalse, msoScaleFromTopLeft
    Application.CommandBars("Format Object").Visible = False
    Rows("27:27").Select
End Sub

Or even.

    With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .GetFromClipboard
        strClip = .GetText
    End With

Originally Posted by bakerman2

Or even.

    With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .GetFromClipboard
        strClip = .GetText
    End With

Yes, that works sweetly too , many thanks!

Sub InsertPicture_Gf4_bakerman2_2()

Dim MyData As Object 'Was "DataObject"
Dim strClip As String
' "Or even...
' Not sure what edits to make, but best guess ...
' YES, works!

    With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .GetFromClipboard
        strClip = .GetText
    End With

' Set MyData = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
' MyData.GetFromClipboard
' strClip = MyData.GetText
Range("A30").Select
    ActiveSheet.Pictures.Insert(strClip).Select
    Selection.ShapeRange.ScaleHeight 0.85, msoFalse, msoScaleFromTopLeft
    Application.CommandBars("Format Object").Visible = False
    Rows("27:27").Select
End Sub

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+.