Excel VBA: Copying Pictures from image controls to

Question

I have a series of images that I need to display multiple times on both forms (via image controls) and on worksheets (via activeX image controls). I know that I could keep the files externally and use the loadpicture method; but there is something I want to avoid if possible.

I also know I could save and load- but again I would rather not use an external file write to perform the task. Ideally, everything will stay embedded and hidden within the file itself.

I think there maybe a solution in using the clipboard- but I couldn't get the syntax to work. The object is embedded always in the same location(s); it never moves or changes size or other properties (beyond .visible). So what I would really like to do is something simple like;

Sheet1.oleobjects("toImage").object.picture = frm1.fromImage.picture

**Edit: **
I think I've found a solution to this; but still have a related question.

I worked out that I could do what I want if I embed a series of activeX images on a sheet; then reference them in the actual controls / objects I want. So;

Sheet1.oleobjects("toImage").object.picture=Sheet1.oleobjects("FromImage").object.picture

or

frm1.Controls("toImage").picture = Sheet1.oleobjects("FromImage").object.picture

But, the below doesn't work when I try to do the same using an inserted picture (a shape object);

frm1.toImage.picture =  sheet1.shape("FromImage").picture

..isn't valid syntax. It seems the only thing I can do with them is copy them- I couldn't use them to set the picture of another object without using the clipboard.

The solution above works for me (using a series of activeX image objects rather than pictures)- but I am curious why I can't do with using a standard picture (shape).

Answer 1

If you want to add a picture to your Excel sheet use something like this:
(You can change ActiveSheet to your favorite sheet)

Dim aSheet As Worksheet
Dim aShape As Shape
Set aSheet = ActiveSheet
Set aShape = aSheet.Shapes.AddPicture("<FileName>", msoFalse, msoTrue, 120, 120, 200, 200)

And for more details :

Function AddPicture(Filename As String, LinkToFile As MsoTriState, SaveWithDocument As MsoTriState, Left As Single, Top As Single, Width As Single, Height As Single) As Shape

Function AddPicture2(Filename As String, LinkToFile As MsoTriState, SaveWithDocument As MsoTriState, Left As Single, Top As Single, Width As Single, Height As Single, Compress As MsoPictureCompress) As Shape

---

And if you want to load a picture to your Image component on your form :
(Add an Image component to your form [: Image1])

Set Image1.Picture = LoadPicture("<FileName>")