+ Reply to Thread
Results 1 to 42 of 42

Inserting image into userform controls strictly through code

  1. #1
    Registered User
    Join Date
    10-03-2012
    Location
    San Antonio TX
    MS-Off Ver
    Excel 2010
    Posts
    22

    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

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Inserting image into userform controls strictly through code

    if you inserted the image into an activex image control on the worksheet then you may just assign the picture property of that control to the picture property of the other control. if you inserted as a picture then you will need to use the copypicture method of the shape and then use api calls to paste from the clipboard to the image control-do a search for stephen bullen's pastepicture code.

    edit: you can get the sample pastepicture file from this page: http://www.oaltd.co.uk/excel/default.htm
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Registered User
    Join Date
    10-03-2012
    Location
    San Antonio TX
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Inserting image into userform controls strictly through code

    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!

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Inserting image into userform controls strictly through code

    you definitely have 64bit office not just 64bit windows?

  5. #5
    Registered User
    Join Date
    10-03-2012
    Location
    San Antonio TX
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Inserting image into userform controls strictly through code

    Definitely.

  6. #6
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Inserting image into userform controls strictly through code

    ok-I can't test this properly at the moment but I think it should work in either 32 or 64bit office. too long to post in the thread so it's a text file
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    10-03-2012
    Location
    San Antonio TX
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Inserting image into userform controls strictly through code

    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

  8. #8
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Inserting image into userform controls strictly through code

    yes that's correct. you need to copy the picture and then use code such as
    Please Login or Register  to view this content.
    in your userform (untested as I am using a mac at the moment)

  9. #9
    Registered User
    Join Date
    10-03-2012
    Location
    San Antonio TX
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Inserting image into userform controls strictly through code

    Ok. Where exactly is the code to be pasted?

    DJams

  10. #10
    Registered User
    Join Date
    10-03-2012
    Location
    San Antonio TX
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Inserting image into userform controls strictly through code

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

    Error.png

  11. #11
    Registered User
    Join Date
    10-03-2012
    Location
    San Antonio TX
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Inserting image into userform controls strictly through code

    Putting it in its own Module worked to this point:

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

    Generated a Mismatch Error

  12. #12
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Inserting image into userform controls strictly through code

    it should be in its own module

    I think we need to change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    10-03-2012
    Location
    San Antonio TX
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Inserting image into userform controls strictly through code

    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

  14. #14
    Registered User
    Join Date
    11-12-2007
    Posts
    8

    Re: Inserting image into userform controls strictly through code

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

  15. #15
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Inserting image into userform controls strictly through code

    64bit-we already discussed that ;-)

    there were no syntax errors in the code since it compiled fine so you should not have needed to change any parentheses and not all the declarations need ptrsafe due to conditional compilation. I will test and get back to you asap

  16. #16
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Inserting image into userform controls strictly through code

    ok this code works for me on 64bit win7 with 64bit office 2013
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    10-03-2012
    Location
    San Antonio TX
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Inserting image into userform controls strictly through code

    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.

  18. #18
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Inserting image into userform controls strictly through code

    change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    I assume the real code does not copy to the sheet and populate the form image at the same time ;-)

  19. #19
    Registered User
    Join Date
    10-03-2012
    Location
    San Antonio TX
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Inserting image into userform controls strictly through code

    Well...yes it does. It gets the file name and copies the photo into a worksheet, selects the photo, copies to the clipboard (supposedly) and then pastes the picture from the clipboard into the image control picture property on a userform. Of course, it hasn't worked yet <g>.

    Let me try yours.
    Last edited by DJams; 10-24-2012 at 10:43 AM.

  20. #20
    Registered User
    Join Date
    10-03-2012
    Location
    San Antonio TX
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Inserting image into userform controls strictly through code

    I am still getting a mismatch error.

  21. #21
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Inserting image into userform controls strictly through code

    on which line? my code works for me in 64 bit office.

    if you are actually loading the picture to sheet and userform at the same time there are far simpler ways-just use
    Please Login or Register  to view this content.

  22. #22
    Registered User
    Join Date
    10-03-2012
    Location
    San Antonio TX
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Inserting image into userform controls strictly through code

    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

  23. #23
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Inserting image into userform controls strictly through code

    the previous post showed how to load the image control from the image on the sheet-using CopyPicture and then the PastePicture code. assuming you didn't delete the picture from the sheet you can simply copy the picture from the sheet whenever you load the form

  24. #24
    Registered User
    Join Date
    10-03-2012
    Location
    San Antonio TX
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Inserting image into userform controls strictly through code

    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.

  25. #25
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Inserting image into userform controls strictly through code

    no-the code copies the picture and pastes it into the image. that's the whole point of this thread, is it not?

  26. #26
    Registered User
    Join Date
    10-03-2012
    Location
    San Antonio TX
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Inserting image into userform controls strictly through code

    Yes. But if I close the file and reopen, without a reference to the data to repopulate the controls in the userform...pictures, text, whatever..., when the userform is opened the controls are blank unless the data is stored permanently in the controls, which it isn't. If I want to load and show Userform1.Image1.Picture with information that was input prior to the file or the userform being closed, I will need to have a valid reference to the photo. The next time the file is opened, the photo's original source file may no longer be available, hence the need for a reference to the date stored in the excel file.
    Last edited by DJams; 10-24-2012 at 12:09 PM.

  27. #27
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Inserting image into userform controls strictly through code

    which is why you load the picture onto the sheet I assume. the form control can then load it from there without the original source file being present.

  28. #28
    Registered User
    Join Date
    10-03-2012
    Location
    San Antonio TX
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Inserting image into userform controls strictly through code

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

  29. #29
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Inserting image into userform controls strictly through code

    in the initialize event of the form you can use code like
    Please Login or Register  to view this content.

  30. #30
    Registered User
    Join Date
    10-03-2012
    Location
    San Antonio TX
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Inserting image into userform controls strictly through code

    I can make this work with a little more info. Does excel number pictures according to what is on a sheet. For example:

    Please Login or Register  to view this content.
    and

    Please Login or Register  to view this content.
    Does Excel number the pictures 1 through X based on the number in a sheet or the number in a workbook?
    Last edited by DJams; 10-24-2012 at 03:46 PM.

  31. #31
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Inserting image into userform controls strictly through code

    yes it starts at 1 on each sheet. you can also name the pictures as you add them and refer to them by name which is usually better

  32. #32
    Registered User
    Join Date
    10-03-2012
    Location
    San Antonio TX
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Inserting image into userform controls strictly through code

    ok. let me try.

  33. #33
    Registered User
    Join Date
    10-03-2012
    Location
    San Antonio TX
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Inserting image into userform controls strictly through code

    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.

  34. #34
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Inserting image into userform controls strictly through code

    I have run the pastepicture code repeatedly without any problem so I don't think it's that-can you post a sample workbook that crashes for you?

    in the meantime you may want to try using activex image controls on the sheet and just assign the picture to your userform image control without all the api code

  35. #35
    Registered User
    Join Date
    10-03-2012
    Location
    San Antonio TX
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Inserting image into userform controls strictly through code

    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

  36. #36
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Inserting image into userform controls strictly through code

    I'm assuming you want to store all the pictures in the workbook and then load different ones into the userform as required? so at design time you load all the pictures into the image controls (once they are loaded, there is no link to the original file) and you can then simply load the relevant picture from the relevant control into the image on the userform.

  37. #37
    Registered User
    Join Date
    10-03-2012
    Location
    San Antonio TX
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Inserting image into userform controls strictly through code

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

  38. #38
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Inserting image into userform controls strictly through code

    simply
    Please Login or Register  to view this content.
    for instance

  39. #39
    Registered User
    Join Date
    10-03-2012
    Location
    San Antonio TX
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Inserting image into userform controls strictly through code

    Ok, Joseph. It works! I removed the PastePicture coding (don't need it). I insert 80 image controls into ("Photos").
    Had a real problem with the image controls because of the original pictures sizes. So what I did was took all the photos in the file and resampled them to fit unlimately in the image control on the userform. Then I put the code in you provided that sets the picture property of the userform image control from the picture in the worksheet control. Deleted all the original picture files and opened and closed the userform and the workbook a number of times. Everything was still there when I re-opened them. This is what I was looking for and I appreciate your patience in working with me on this. Thank you very much.

    Completed code:

    Please Login or Register  to view this content.
    I also just copied the "Set" line shown above into the call for the userform so when it is re-opened it displays correctly.

    Excellent. This can be marked as solved!

    DJams
    Last edited by DJams; 10-30-2012 at 01:00 PM.

  40. #40
    Registered User
    Join Date
    02-19-2014
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    1

    Re: Inserting image into userform controls strictly through code

    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

  41. #41
    Registered User
    Join Date
    06-18-2017
    Location
    Ukraine
    MS-Off Ver
    16
    Posts
    1

    Re: Inserting image into userform controls strictly through code

    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 ?

  42. #42
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,110

    Re: Inserting image into userform controls strictly through code

    pkapa welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1