Loop through image controls on worksheet

  • Thread starter Thread starter Paul Mathews
  • Start date Start date
P

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.
 
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.
 
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.
 
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")
 
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
 
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
 
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.
 
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.
 
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.

Paul said:
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.
Click to expand...
 
Back
Top