Copy image in userform to a worksheet

[YingFa]

Hi,

Would it be possible to have your help to get a code that allows to copy an image loaded in a user form to worksheet 4 in cells A12:N12 and M12:BL12?

I have the below code but I do not know how to make it work for the range of cells where I need to insert the pictures. I would appreciate a lot your help.

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

 

[Yongle]

Here is one way
- a copy of the image is saved to folder
- image imported to sheet
- saved image deleted

For the first test ... do exactly as below

1. Open a NEW workbook and SAVE it
2. Insert a userform
3. Insert Image Control in UserForm (it will be named auto-named Image1) and drag it to desired size
4. Show Properties window
5. With Image1 properties diplayed
- click Picture property and click small button with 3 dots to insert an image of your choice
- amend PictureSizeMode proprty to PictureSizeModeStretch
6. Place code below in userform code module
7. Run the userform - the image is placed in the sheet when the userform loads

After first test modify to suit your needs

Private Sub UserForm_Activate()
    PicToSheet Me.Image1, ActiveSheet
End Sub

Private Sub PicToSheet(picControl, sht As Worksheet)
    Dim p As String, L As Double, T As Double, H As Double
    p = ThisWorkbook.Path & "\" & Format(Now, "yymmdd hhmmss") & "bmp"
'save temporary image to folder
    SavePicture picControl.Picture, p
'embed image in sheet
    L = sht.Cells(1, 12).Left: T = sht.Cells(1, 12).Top
    With sht.Shapes.AddPicture(Filename:=p, linktofile:=msoFalse, savewithdocument:=msoCTrue, Left:=L, Top:=T, Width:=123, Height:=49.5)
        .Placement = xlMove
        .OLEFormat.Object.PrintObject = msoTrue
        .OLEFormat.Object.Locked = msoTrue
    End With
'delete temporary file
    Kill p
End Sub

 

[Yongle]

I forgot to mention that the above code is something I found and modified
- see Tim Williams contribution https://stackoverflow.com/questions/39521019/copy-picture-from-userform-to-spreadsheet

 

[YingFa]

Hello,

Thank you very much for your help on this and sorry for taking such a long time to answer back. I just tried the code and it is given me "Run-time error 75": Path file access error. When I debt it highlights this line in the code SavePicture picControl.Picture, the when I click the highlighted line it say msoTrue= -1. Do you have any idea why is showing error?

Again, thank you very much.

 

[YingFa]

I have figured it out. Completely mi mistake. I forgot to save the workbook as you kindly explained in your detailed steps above. I have tried the code and it works perfectly! Thank you very much!

 

[Yongle]

Glad it worked for you
Thanks for your feedback

 

[YingFa]

Hello again,

Would it be possible to have your help in something else? How do I remove the pictures once I do not need them anymore?