Inserting image into userform controls strictly through code

I have looked a many, many forums and posts to find out how to do this. I have gleaned enough information to take me to a certain point but now I am at an impasse.
I have written a macro that will copy a picture into a shape on a worksheet sized as I wish. If I select and copy the shape to the clipboard (Ctrl-C) and then paste (Ctrl-V) into Userform.Image.Picture properties in the VBA editor the picture (presumably as a shape) is inserted into the image control and zoomed so as to fit. All that is great (since it is not a link to an external image any longer but is now "stored" in the excel workbook file).
The problem is that I want to do this through VBA, not through keystrokes.
Where I get stuck is "pasting" the clipboard data into the image control properties box and I can find no coherent information on how to do that.
My understanding is that once an image is inserted into a shape, it is assigned a new name by excel. Can I retrieve the name and code that into Image.Picture properties?
Is there a way assign a variable to the clipboard data (presumably the shape with the image inserted) that can be used as an "Image1.Picture=variable" line of code? If so, how so?
The whole key here so as not to rehash a great deal of Q/A is to have the image in the document so pictures (source files) can be deleted or moved with affecting the excel doc.

DJams

Sorry, Joseph. That was so far over my head I got a nose bleed getting to it. But I tried looking at the info on the link that you kindly provided. Unfortunately the code is for 32 bit and I'm running W7/64 with the matching excel version. Fixing the code to work is way above me. Any idea where I can get the same or similar examples already coded for 64 bit?


Thanx!

Definitely.

Ok. I'm with this...sort of. The next question is, where does it go? Does it go in the code corresponding to a commandbutton_click event or does it establish parameters during workbook opening, or some other event? If I am understanding the code correctly it is looking for information already on the clipboard, it is not copying info to the clipboard and then modifying it. Is that correct?

DJams

Ok. Where exactly is the code to be pasted?

DJams

I tried putting it in the Userform and got this error:

Error.png

Putting it in its own Module worked to this point:

RetVal = OleCreatePictureIndirect(PicInfo, Ref_ID, True, IPic)

Generated a Mismatch Error

That did not make a difference. I did notice several function declarations that did not have PtrSafe in the line and a couple that were missing the "(" or had extras. After correcting them the mismatch error remains.

DJams

Your Excel is 32-bit or 64-bit? I test in Excel2010 32-bit is fine.

I restored the code to original. I removed the code I was using (selection.copy) to get the photo to the clip board and copied a photo manually (Ctrl-C). When running the new code I did not get an error but neither was the photo put into the image control. Here's my code:

Please Login or Register  to view this content.

Probably not to elegant.

I am still getting a mismatch error.

Ok. I replaced the PastePicture code with what you sent and it works. But now I am back where I started. My whole point in this was to populate the userform Image control with a photo that would be independent of the source photo so when I close the userform and open it again the photo will still be in the control. I was using the code you have listed above...

Please Login or Register  to view this content.

...but because the picture property is a path back to the source it wouldn't work if the original picture file was moved or deleted. The Ctrl-C/Ctrl-V worked because it put a bitmap into the picture property of the image control that was permanent and that is what I am trying to emulate in code. While the new PastePicture code puts the photo in the control, the picture is lost when the userform is closed and reopened. If I put a line of code into the command button that opens and populates the userform's controls in question I will have to have a source for the photo. Is it possible to assign a variable name to the return of the PastePicture procedure that will store the image in the excel file so that it can be referenced by code to load into the image control every time the userform is opened? It must not be dependent on the external-to-excel source file.

DJams

True enough. But to do that I will have to select it, copy the picture to the clipboard and paste it into the control every time I open the userform. There maybe 60 controls and 60 pictures I will need to do this for. It would be too time consuming to do it manually and since the PastePicture code copies from only what is currently existent in the clipboard, without a usable reference to each photo resident in the excel file to be placed into each control it would be like recreating/rewriting the report (that's what this file does) every time it's opened. There must be some way to make this work.

That would be outstanding. My question would be then, how does the form control load the picture from the sheet?

ok. let me try.

Ok, Joseph. I have not added the initialization code you provided. I have been trying the other (see code below) and it works the first time. The second time (after closing the userforms and opening them again), the attempting to input a picture into the now empty image control using the same code (below) causes excel to crash. In order to make the workbook work again I have to reboot the computer.

Please Login or Register  to view this content.

The problem, as I understand it, is that the image control picture property requires a complete filepath to the picture. It will not accept anything else (except a copy/paste of the photo. When I Ctrl-C/Ctrl-V the picture into the properties box the expression (Bitmap) shows up in the line). How does putting the control on the sheet as opposed to the userform change that?

DJams

I would be good with that. What would be the syntax for it?

After much searching, this thread has been the closest I can find to what I am trying to do. I too am trying to have pictures without external references, but I was trying to use an activeX image control on a worksheet to display one of several pictures kept on the worksheet. So it would be something like:

Please Login or Register  to view this content.

It sounded like you were doing something similar at one point and having similar issues to what I've been having, although your solution doesn't work for me (I think because of different syntax for activeX image controls on a worksheet versus a userform image??). Is there no way to point the image control's picture property to a named picture on a worksheet? Or something functionally similar? Would the clipboard option (that was way over my head so far) be an option for this also (or the only option)? Any help would be greatly appreciated. Trying to generate some calcs with appropriate welding details for a client by Tuesday, and I am so frustratingly close but still so far away. Everything I try gives me "Type Mismatch" or "Object Required" errors and I'm stumped. Thanks!
Jason

For MS Office 2010, 2013, 2016 function PastePicture (modified by JosephP) works for:
1. WIN 64-bit + Office 64-bit
2. WIN 32-bit + Office 32-bit
and not works for
WIN 64-bit + Office 32-bit

In this case VBA Project crashes during:
'Create the Picture object.
RetVal = OleCreatePictureIndirect(PicInfo, Ref_ID, True, IPic)

In this article https://technet.microsoft.com/en-us/...2.aspx#compat7 wrote that "In-place OLE activation might not work if the version of Office 2013 doesn’t match registered applications".

Anybody knows how to solve this problem for WIN 64-bit + Office 32-bit ?

i went through a lot of heartache with this when i updated my Laptop to a 64bit system. So thank you all for your input here.
i am using it in Excel Forms, putting a Logo onto a Form depending on who the user is .

Regards to all

Richard