[SOLVED] Programatically control picture property of Image control

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

"Brassman" wrote:

> 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

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" wrote:

> 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" wrote:
>
> > 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

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.

"Brassman" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > 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

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 **** 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.


"Brassman" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > 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

> 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

"K Dales" <[email protected]> wrote in message
news:[email protected]...
> 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 **** 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.
>
>
> "Brassman" wrote:
>
> > 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" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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