Loop through image controls on worksheet

[Paul Mathews]

I'm attempting to set the picture property of several image controls on a
single worksheet (not on a user form). For example, three image controls
(named Image1, Image2, and Image3) on Sheet 1:

Sheet1.Image1.Picture = LoadPicture("C:\Pic1.jpg")
Sheet1.Image2.Picture = LoadPicture("C:\Pic2.jpg")
Sheet1.Image3.Picture = LoadPicture("C:\Pic3.jpg")

Can anyone suggest a way to set the picture property for each image by
looping rather than repeating the loadpicture for each image as above?
Thanks in advance.

 

[Joel]

method 1
for i = 1 to 3
set pict = LoadPicture("C:\pic" & i & ".jpg")

next i


method 2

folder = "c:\"
PictName = array(Pic1.jpg,Pic2.jpg,Pic3.jpg)
for each picture in PictName
set pict = LoadPicture(Folder & picture)
next picture


You can set the picture properties using pict.left = .5 or any equivalent
property.

 

[Paul Mathews]

Joel, thanks for the speedy reply. I'm not following how the code loads each
of the three image controls with its own jpg image. That is, how do I
declare your variable, "pict" so that it is an object that refers to the
Image1 control when i=1, Image2 when i=2, and Image3 when i=3? Thanks again,
much appreciated.

 

[Joel]

You can declare Pict as object.

I was expecting you to add all your code inside the loop

for i = 1 to 3
set pict = LoadPicture("C:\pic" & i & ".jpg")
pict.left = 50
pict.top = 100
next i


Pict in my examples would refere only to the latest pict loaded. Each time
a new pict is loaded it would erase the old information.


If you need to refence each of the pictures yo ucan do something like this
pict1 = LoadPicture("C:\Pic1.jpg")
pict2 = LoadPicture("C:\Pic2.jpg")
Pict3 = LoadPicture("C:\Pic3.jpg")

You can create an arrry of pictures

Dim pict(3)
pict(1) = LoadPicture("C:\Pic1.jpg")
pict(2) = LoadPicture("C:\Pic2.jpg")
Pict(3) = LoadPicture("C:\Pic3.jpg")

The loop for this would be this
for i = 1 to 3
pict(i) = LoadPicture("C:\Pic" & i & ".jpg")
next i

If you uknow the names of the pictures yo ucan od this

set pict = shapes("Pic1.jpg")

 

[Dave Peterson]

Option Explicit
Sub testme()
Dim iCtr As Long
For iCtr = 1 To 3
Sheet1.OLEObjects("Image" & iCtr).Object.Picture _
= LoadPicture("c:\pict" & iCtr & ".jpg")
Next iCtr
End Sub

 

[Paul Mathews]

Dave, Joel, thank you both very much. Dave, the code you provided is
precisely what I was looking for (I couldn't quite figure out how to use the
OLEObjects method so that I could specify a string name for each image
control and you nailed it with your solution). I appreciate the time you
took to provide this solution to me and the rest of the Excel community.

Paul Mathews

 

[Dave Peterson]

I don't normally put image controls on worksheets. I just plop the pictures
where I want them.

Is there a reason you don't use the plain old pictures?

Just curious.

 

[Paul Mathews]

Hi Dave, yes you're right, this is a pretty darned strange thing to do.
Let's call it an experiment at this point. I guess it's best described as
something of an Excel photo viewer (i.e., click a forward or back button and
see pictures change on a worksheet as well as text; the text and picture file
names are sourced from a data table in another worksheet). I may well end up
doing the standard insertion of pictures from files into the worksheet but I
was curious about this approach (image controls do have the nice feature of
providing automatic sizing of a loaded picture using the PictureSizeMode
property). Thanks again for indulging me.

 

[Dave Peterson]

Ah. That seems like a reasonable thing to try.

If you're working with pictures, you may want to use http://www.irfanview.com.
It's a nice picture viewer with lots of features. You can make a pdf file of
thumbnail sized pictures.

And http://picasa.google.com may be of use if you want a free basic photo
editor/organizer.

Hi Dave, yes you're right, this is a pretty darned strange thing to do.
Let's call it an experiment at this point. I guess it's best described as
something of an Excel photo viewer (i.e., click a forward or back button and
see pictures change on a worksheet as well as text; the text and picture file
names are sourced from a data table in another worksheet). I may well end up
doing the standard insertion of pictures from files into the worksheet but I
was curious about this approach (image controls do have the nice feature of
providing automatic sizing of a loaded picture using the PictureSizeMode
property). Thanks again for indulging me.