Programatically control picture property of Image control

[Brassman]

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

 

[K Dales]

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

 

[Brassman]

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

 

[K Dales]

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.

 

[K Dales]

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.

 

[Peter T]

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