Shape click to insert picture automation

Hello everyone,

I am hoping to get help solving a VBA code for clicking on a white square (shape) which will then lead to the file explorer window to insert a picture into the same white square. For instance I am looking to automate the following operation with one left click on the shape:

Right click(sub menu pulls up) Fill, Picture, From a File

Once the picture is chosen, place chosen image into same box that was clicked. I want to contain the size of the picture to the shape size.

I tried another option, which almost gets me there, but it pulls the picture in and does not insert it into the shape, but rather just drops it onto the worksheet. For some reason I cannot insert the code I tried without getting firewall blocked.

Thanks in advance for any help.

I am bumping this up as I did not get any suggestions. Any direction towards my goal is appreciated. Thank you.

One last try to bump this one up before I try a different approach. Any advice here? Can what I am asking be done? Thanks

I have got very close to the result I am looking for with this VBA. The only issue I now have is trying to figure out how to change my code to EMBED the picture instead of linking to it. I want to be able to move the file around without it depending on the original picture link. Please take a look at my code and let me know what I can change if anything to achieve this. Thank you.

Please Login or Register  to view this content.

Anyone have any thoughts on how to change my code to EMBED the image as opposed to link?

Please Login or Register  to view this content.

The shape you clicked is called "Rectangle 1" in this case

Please Login or Register  to view this content.

I believe that is what I was looking for. I am curious though, is there anyway to substitute the need for calling out the name of a rectangle in the vba? What I mean is, could I apply this vba to say 10 rectangles on a sheet and it will refer to the "active" shape as opposed to needing to make 10 different vba codes all calling out a rectangle name and assigning each vba to the specific shape? Could I just apply the same code to the 10 rectangles and just have the code execute on the active shape? I hope I'm explaining my need clearly. Thank you for the help thus far. It has gotten me much closer to what I am needing to achieve.

Try so.

Please Login or Register  to view this content.

If your shapes have meaningful names that relate to known data, the attached might be better.

@IFSSCref
Any of the suggestions working?

@jolivanes
So sorry for the late response. I must have missed the notification that you responded. I am attaching the file I am working on to hopefully help show what I am going for. I tried your updated code, but I would just get a window that popped up saying "Select a shape before you continue". This was when I was clicking on the shape to insert the picture. Let me just sum up what I am looking for and what I may need to account for.

There are several larger white shapes on this sheet. I want to be able to click on the shape and bring up an explorer window to navigate to any picture I want to place. Once the picture is chosen I need it to be EMBEDED in the sheet and conform to the shape of the box clicked on. I need to be able to apply this to all shapes on this sheet to perform the same way. I also will need the vba to take into account if I need another blank sheet of this same format added. I believe conforming the vba to shape names would cause me issues as I will never know how many pages I may need for one report and will not have employees renaming shapes at all. Thank you again for all your help thus far. I believe I am ALMOST there.

Use this code to assign the same macro to all rectangles.

Please Login or Register  to view this content.

This will be all you need for all 675 rectangles. (tongue in cheek)
Select any rectangle in your sheet.

Please Login or Register  to view this content.

After this thread being open since July you have completely answered my question! Thank you so much! This one has been a struggle for me way too long. I really appreciate your time spent helping me with this. I would give more reputation if I could. Thanks again!

Thank you for letting us know that all is well now in you part of the "Excel World"
Good Luck and thanks for the rep.

Hi Jolivanes,

This helped me out, also. Thank you.

It inserts the image into the shape, and maintains the original size of the shape (which is what I hoped for). However, I'd like to keep the original aspect ratio of the picture, while maintaining the size of the shape (so there'd be white space on the edges if the original picture were too narrow, etc).

Is there a straightforward way to do this?

Thank you,

I'll experiment with those, and open an new thread if I can't get it to work.

For reference, this is the formula I referred to in my earlier post.

Please Login or Register  to view this content.