Load Image to User Form

FracinDean

Board Regular
Joined
Jul 11, 2011
Messages
110
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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:

VBA Code:
Sub loadLegend()       
    Load myLegend
End Sub

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

VBA Code:
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.
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

FracinDean

Board Regular
Joined
Jul 11, 2011
Messages
110
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
May 16, 2017
Messages
771
1594162554468.png
 
Solution

FracinDean

Board Regular
Joined
Jul 11, 2011
Messages
110
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
VBA Code:
.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

Well-known Member
Joined
May 16, 2017
Messages
771
I think you may have typed it wrong. More likely it's this:
VBA Code:
      .Picture = PastePicture(xlPicture)      ' paste range of info copied
 

FracinDean

Board Regular
Joined
Jul 11, 2011
Messages
110
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I think you may have typed it wrong. More likely it's this:
VBA Code:
      .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

Well-known Member
Joined
May 16, 2017
Messages
771
VBA Code:
       .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

Board Regular
Joined
Jul 11, 2011
Messages
110
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 11, 2011
Messages
110
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,359
Messages
5,601,157
Members
414,431
Latest member
JustmemyselfandI

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top