Programatically control picture property of Image control

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a workbook with a table similar to this:
Item No. | Description | PicPath
ITEM1 | Item 1 description | C:/Images/a pic.jpg
ITEM2 | Item 2 description | C:/Images/another pic.jpg

On other sheets, I have vlookup functions calling records from the table. I
also have image controls on those sheets, named the Item No.s (ITEM1, ITEM2).

Is there a way I can programatically loop through those images and set the
picture property? This is the basic idea of what I want to do, but it doesn't
work. How do I need to fix it? Thank you very much.


Code:

Sub GetPics()
Dim c as Image
For each c in Controls
c.Picture = LoadPicture(Application.WorksheetFunction.VLookup(c.Name,Range
_("DataTable"),3,False))
Next c
End Sub
 
Fixes:
There is no "Controls" collection in the Excel application, or even for a
workbook or worksheet, so you can't use your For Each loop this way. If the
Images on you sheets are from the Controls toolbox they are actually Shape
objects with a Shape.Type of 12. So you need to iterate through the Shapes
and then check the shape type to see if it is an image or not.

I think this will work:
Sub GetPics()
Dim XLShape as Shape
For each XLShape in Sheets("ImageSheet").Shapes
If XLShape.Type = 12 Then
XLShape.DrawingObject.Object.Picture = LoadPicture _

Application.WorksheetFunction.VLookup(c.Name,Range("DataTable"),3,False))
End If
Next XLShape
End Sub
 
Thanks. I had just figured out another way to do with the OLEobject
collection, and I came here to post my solution. Is either or these ways
better than the other? Here's what I came up with:

Sub Macro1()

Dim c As OLEObject
Dim x As Integer
For x = 1 To Worksheets.Count
On Error GoTo ErrHand
For Each c In Worksheets(x).OLEObjects
With c
.Object.Picture =
LoadPicture(Application.WorksheetFunction.VLookup _(.Name,
Sheet1.Range("A1:E3"), 5, False))
End With
Next c
ErrHand:
Next x

End Sub
 
Hard to say which, if either, is better; best thing would be to try running
the code with several images and see which is most efficient (fastest), but I
doubt you will see much difference as the time spent in locating and loading
the file from disk will be a lot bigger than any time spent executing the
code.

Congrats on figuring this out though! There are often several ways to do
the same thing.
 
I did just think of one potential issue with your code: yours uses the error
handler to suppress problems if there is another (non-image) type of
OLEObject on the sheet; that works fine but I don't like suppressing errors
this way since it is possible that something else could generate an error,
and if so I would want to know about it. It is a picky issue, though, and
the chance of it causing a problem seems minimal in this case. If you were
as picky as me you could either test the type of OLEObject (similar to how I
am detecting if the Shape contains an Image) or you could have your error
handler determine the error type (number) and ignore the error generated by
trying to do a LoadPicture on a non-image object; otherwise show you that
error code.

I know - I am overly anal in my coding, but I always think you should plan
for everything that could go wrong. That is based on many years (won't tell
you HOW many) of hard experience.
 
If you were
as picky as me you could either test the type of OLEObject (similar to how I
am detecting if the Shape contains an Image)

something like:

Dim c As OLEObject
For Each c In Worksheets(x).OLEObjects
If TypeOf c.Object Is MSForms.Image Then

Regards,
Peter T
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top