none
Copy an Image from a User form Picture Box to Worksheet Picture Box RRS feed

  • Question

  • I am new to VBA so apologies in advance...
    I have a user form ("UserForm1") where an image is selected via a command button file dialog function and then displayed in a picture box ("RRPPicture").
    When the user clicks another command button to progress ("NextPage1"), I want the image held in ("RRPPicture") to automatically be inserted into a fixed cell location or picture box on a Worksheet called ("Report").
    Does anyone have any ideas, is it possible, I really have scoured the forums I know of...
    Many thanks in advance!
    Wednesday, August 6, 2014 10:53 AM

Answers

  • Reply by Fei Xue has possibly pointed you in the right direction but here is a little more information. The  PictureSizeMode for the image on the worksheet should also match the one used for the Image on the Userform. In the example below I have used the property "3 - fmPictureSizeModeZoom". Therefore this property should also be set to 3 when copying the picture to the worksheet.

    The other thing I have done is match the actual size of the picture on the worksheet to the one on the Userform by setting the Width and Height to the same as the image on the Userform. Note that these do not need to be the same and you can modify them for the worksheet.

    Private Sub btnCopyPicture_Click()
        Dim shpPicture As OLEObject
       
        With Worksheets("Report")
            Set shpPicture = .OLEObjects.Add(ClassType:="Forms.Image.1", _
                            Link:=False, _
                            DisplayAsIcon:=False, _
                            Left:=.Cells(3, "C").Left, _
                            Top:=.Cells(3, "C").Top, _
                            Width:=Me.RRPPicture.Width, _
                            Height:=Me.RRPPicture.Height)
        End With
       
        With shpPicture
            .Object.PictureSizeMode = 3        'fmPictureSizeModeZoom
            .Object.Picture = Me.RRPPicture.Picture
        End With

    End Sub


    Regards, OssieMac

    • Marked as answer by Sledgeham-ar Thursday, August 7, 2014 12:15 PM
    Thursday, August 7, 2014 11:55 AM
  • Hi,

    Based on the description, you want to insert a image into the specific cell from image control on the userForm. Here is a sample for your reference:

    Set img1 = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Image.1", Link:=False, _
            DisplayAsIcon:=False, Left:=ActiveSheet.Cells(1, 1).Left, Top:=ActiveSheet.Cells(1, 1).Top, Width:=123, Height:= _
            49.5)
         img1.Object.Picture = Me.Image1.Picture

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, August 7, 2014 5:55 AM
    Moderator

All replies

  • Hi,

    Based on the description, you want to insert a image into the specific cell from image control on the userForm. Here is a sample for your reference:

    Set img1 = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Image.1", Link:=False, _
            DisplayAsIcon:=False, Left:=ActiveSheet.Cells(1, 1).Left, Top:=ActiveSheet.Cells(1, 1).Top, Width:=123, Height:= _
            49.5)
         img1.Object.Picture = Me.Image1.Picture

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, August 7, 2014 5:55 AM
    Moderator
  • Reply by Fei Xue has possibly pointed you in the right direction but here is a little more information. The  PictureSizeMode for the image on the worksheet should also match the one used for the Image on the Userform. In the example below I have used the property "3 - fmPictureSizeModeZoom". Therefore this property should also be set to 3 when copying the picture to the worksheet.

    The other thing I have done is match the actual size of the picture on the worksheet to the one on the Userform by setting the Width and Height to the same as the image on the Userform. Note that these do not need to be the same and you can modify them for the worksheet.

    Private Sub btnCopyPicture_Click()
        Dim shpPicture As OLEObject
       
        With Worksheets("Report")
            Set shpPicture = .OLEObjects.Add(ClassType:="Forms.Image.1", _
                            Link:=False, _
                            DisplayAsIcon:=False, _
                            Left:=.Cells(3, "C").Left, _
                            Top:=.Cells(3, "C").Top, _
                            Width:=Me.RRPPicture.Width, _
                            Height:=Me.RRPPicture.Height)
        End With
       
        With shpPicture
            .Object.PictureSizeMode = 3        'fmPictureSizeModeZoom
            .Object.Picture = Me.RRPPicture.Picture
        End With

    End Sub


    Regards, OssieMac

    • Marked as answer by Sledgeham-ar Thursday, August 7, 2014 12:15 PM
    Thursday, August 7, 2014 11:55 AM
  • Thank you so much, if you were here I would kiss you!
    Thursday, August 7, 2014 12:17 PM
  • Hi,

    A quick question. I am playing about with where to position this on the form but I cannot delete the object? Please can you advise how I do this? 

    Also, for some reason it appears that every time I click on the picture in the worksheet it gets bigger and there is no way to reduce it.

    I really want it to be in a fixed cell or picture box and selectable once you are in the worksheet but not get bigger every time you click it.

    Thursday, August 7, 2014 1:03 PM
  • Hi,

    A quick question. I am playing about with where to position this on the form but I cannot delete the object? Please can you advise how I do this? 

    Also, for some reason it appears that every time I click on the picture in the worksheet it gets bigger and there is no way to reduce it.

    To delete select the Developer ribbon and click the Design mode icon and then you can select and delete the object. Click Design mode icon again to turn it off.

    On the second question I will have a look at it but can you tell me what PictureSizeMode are you using and also is the worksheet zoom set to 100%t because I know that sometime plays havoc with object size on worksheets.


    Regards, OssieMac

    Thursday, August 7, 2014 8:24 PM
  • With further testing I have found that the method I previously posted makes the Excel files very large. Details as follows: (Picture had been loaded into Photoshop first and then saved as low resolution.)

    Picture size after saving as low resolution: 46KB

    Excel file prior to loading a picture on to the Userform: 30KB

    Excel file after Loading Picture on Userform:  33KB

    Excel file after copying picture to worksheet: 632KB

    I then developed a different method of copying the picture to the worksheet. It involves saving a copy of the picture on another worksheet (can be a hidden worksheet) and naming it the same as the Image control on the userform. The picture on the worksheet is not OLE type but is the same as you get if you simply use Insert -> Picture on the worksheet. Following results of file size.

    Picture size after saving as low resolution: 46KB

    Excel file prior to loading a picture on to the Userform: 30KB

    Excel file after Loading Picture on Userform:  78KB

    Excel file after copying picture to worksheet: 78KB

    Therefore even with the additional copy of the picture on another worksheet, the file still finishes up much smaller.

    I have uploaded the workbook to OneDrive at the following link. It is zipped so Download and unzip into a folder. A sample picture is included in the zipped package.

    http://1drv.ms/1r3NElZ

    I have included a lot of comments in the code but feel free to get back to me if there is anything you do not understand in it.


    Regards, OssieMac

    • Proposed as answer by skysmp Saturday, February 16, 2019 10:25 AM
    • Unproposed as answer by skysmp Saturday, February 16, 2019 10:26 AM
    Friday, August 8, 2014 1:59 AM
  • Hi OssieMac,For some reason this code is now erroring. I get "Run time error 1004, cannot insert object". Please could you help .....????

    I have uploaded to one -drive but it won't let me insert a hyperlink due to account verification??

    Many thanks

    Wednesday, April 29, 2015 10:27 AM
  • Can't be sure but account verification possibly indicates that an email has been sent to you from OneDrive and you need to confirm and verify by clicking on the link as per the directions in the email and then follow any directions and this verifies the account.

    Are you the same person who originally posted the question on this thread and have simply changed your pseudonym?

    In case you require assistance to upload a file to OneDrive:

    1. Zip your workbooks. Do not just save to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
    2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder.)
    3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
    4. Go to this link.  https://onedrive.live.com
    5. Use the same login Id and Password that you use for this forum.
    6. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
    7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
    8. Right click the file on OneDrive and select Share.
    9. Do NOT fill in the form; Select "Get a Link" on the left side.
    10. Click the button "Create a Link"
    11. Click in the box where the link is created and it will highlight.
    12. Copy the link and paste into your reply on this forum.

    Regards, OssieMac

    Wednesday, April 29, 2015 10:58 AM
  • I followed the instructions and it won't let me post the link...

    I have added the link in my personal Biography

    Sorry, I am the same person, you'll see I have posted under MrsMaker to get the answer for this, I lost my password.

    Wednesday, April 29, 2015 11:24 AM
  • Workbook and VB Code protected by password: mistycarrot469
    Wednesday, April 29, 2015 11:26 AM
  • For some reason this code is now erroring. I get "Run time error 1004, cannot insert object". Please could you help .....????

    OK. Having worked out that it is the Userform that displays and I need to hold the Shift key to open the workbook and suppress the Workbook Open event I can see all that I need.

    However, what do I need to do to replicate the error?

    What line does the code stop on? (You might need to remove the Project protection so that when it stops, it will highlight the line.)


    Regards, OssieMac

    Wednesday, April 29, 2015 11:28 PM
  • Hi, I really appreciate you looking!

    VB Code protected by password: mistycarrot469

    Run the code and click in the first tab of the user form called "RRP Information". The click on the orange button "UPLOAD IMAGE OF RRP PICTURE" select a picture and click OK, then the error happens.

    When you run debug it yellow highlights all of the following code:

    Set shpPicture = .OLEObjects.Add(ClassType:="Forms.Image.1", _
                            Link:=False, _
                            DisplayAsIcon:=False, _
                            Left:=.Cells(3, "C").Left, _
                            Top:=.Cells(3, "C").Top, _
                            Width:=Me.RRPPicture.Width, _
                            Height:=Me.RRPPicture.Height)

    Thursday, April 30, 2015 8:17 AM
  • You have saved the template while still in design mode so I suggest you turn off design mode and re-save the template.

    If the user cancels the save because a file already exists then you can have multiple copies of Excel open and not visible. The user cannot close an application that is not visible without doing so via Task Manager and the user will be unaware that there is still an open version of Excel if they can't see it.

    Personally I think that rather than make the application not visible it would be better to simply open the userform over a blank worksheet and then the user can see if Excel is still open.

    If I open with the Shift key to suppress the Workbook open event and turn off design mode and then run the Workbook Open code then no problem with the image.


    Regards, OssieMac

    Thursday, April 30, 2015 11:11 AM