Load Image to User Form

[FracinDean]

Mr. Excel Forum Members,

I am trying to do something similar to what was described in another thread on this forum, Show worksheet range of cells in a User Form. In my case, I'm trying to copy a named range in my workbook and paste it as a picture into a user form. As far as I can figure out, there are 5 things I must do to get this to work

1) Add a Class module with the code mentioned in the linked thread. Done.
2) Add a user form. I added a user form called "myLegend".
3) Write a sub to copy the desired image to the clipboard. Step 3 has been coded and tested by running the sub below and then manually pasting the image, but here is the code for information:

Sub CopyImageToCBoard()
    Application.CutCopyMode = False
    With Worksheets("Steward Data")
        Range("theLegend").CopyPicture Appearance:=xlScreen, Format:=xlPicture
    End With
End Sub

4) Add a procedure to initialize the form:

Sub loadLegend()       
    Load myLegend
End Sub

5) Add the code to the UserForm module to execute whenever the form is initialized.

Private Sub UserForm_Initialize()       'goes in the UserForm code module for form 'myLegend'
    Application.ScreenUpdating = False          'Turn off screen updating
    Call CopyImageToCBoard         'sub that copies the desired range to the clipboard
    With Me.Image1
        .Picture = LoadPicture(vbNullString)    ' clear existing picture
        .Picture = PastePicture(xlPicture)      ' paste range of info copied
    End With
    Application.ScreenUpdating = True           'Turn on screen updating
    myLegend.Show
End Sub

When I run the procedure loadLegend(), I'm getting a compile error on the "With Me.Legend1" statement in the Userform_Initialize procedure, The error message says "Method or data member not found."

I don't know how to fix this error. Any assistance would be appreciated.

 

[rlv01]

It sounds like your user form does not have control named "Image1" on it.

 

[FracinDean]

It sounds like your user form does not have control named "Image1" on it.

Thank you. I'm sure that I didn't add a control. The only control that I want to be able to do is allow the user to close the form when they are finished looking at it. I will look into that.

 

[rlv01]

Select the control on the user form, then look at the Properties window, Name property.

 

[FracinDean]

It sounds like your user form does not have control named "Image1" on it.

Okay, I put the image1 control into my user form. Now I'm getting a compile error on the line

.Picture = PasteSpecial(x1Picture)

and the error message on the words "Paste Special" says Sub or Function not defined. User @cvrband was using this exact statement successfully back in Dec-2019, so I'm puzzled why VBA isn't recognizing it.

 

[rlv01]

I think you may have typed it wrong. More likely it's this:

      .Picture = PastePicture(xlPicture)      ' paste range of info copied

 

[FracinDean]

I think you may have typed it wrong. More likely it's this:

      .Picture = PastePicture(xlPicture)      ' paste range of info copied

Yes, I did type it wrong. I tried a few different things before I came back to the forum. What you typed is what I was using, but the error message is as I said.

 

[rlv01]

       .Picture = LoadPicture(vbNullString)    ' clear existing picture
       .Picture = PastePicture(xlPicture)      ' paste range of info copied

PastePicture and LoadPicture are functions in the Stephen Bullen code module in the earlier thread you referenced. If you don't include code module that defines these functions, you will get that error.

 

[FracinDean]

Thank you for your patience and your many replies to this thread. I have a Class module with the Stephen Bullen code. I also checked that I have a reference to the OLE_Automation in my project.

The OLE_Automation reference is pointing to C:\Windows\SysWOW64\stdole2.tlg. That name sounds like it could be expecting a 64-bit computer, but I have a 32-bit computer, so I don't know if there could be some incompatibility there.

Is there more than one source to obtain the Stephen Bullen code? I am wondering if the version of it that I have could have something wrong with it. When I copied and pasted it into my Class module, there was a single quote in the code that was creating an error message, and the error went away when I got rid of that, but it makes me wonder if the code I have could be corrupted in some other way.

 

[FracinDean]

I don't have a 64-bit computer or 64-bit Excel. I just noticed the following comment from the author whose post thread that I linked. " (this code was originally created by Stephen Bullen and I reduced it to only include VBA7 and 64bit as that is our setup) " which makes me think that I need to find the original Stephen Bullen class module code and use that.