Copy image in userform to a worksheet

YingFa

Board Regular
Joined
Nov 4, 2019
Messages
63
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.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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

Code:
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
Glad it worked for you
Thanks for your feedback (y)
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

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
Back
Top