Search code examples
vbaactivexobject

How to loop through VBA ActiveX (OLE) image controls on worksheet and load picture through the index

I have a bundch of ActiveX controls on a worksheet. I'm wondering if it's possible to Loop through all of them and load pictures depending on certain criteria.

Indeed, I can loop through them and print their name using:

For each obj in SheetName.OLEObjects
    Debug.Print obj.Name
Next obj

I can also load pictures using simply SheetName.Image1.picture = LoadPicture(Path)

What I can't do is to load pictures through a loop like :

For each obj in SheetName.OLEObjects
    SheetName.OLEObjects("Image" & counter).Picture = LoadPicture(Path)
Next obj

It would be tedious to load them using the controls names: SheetName.Image1.Picture=...., SheetName.Image2.Picture...

Hope to find help from you!

Solution

  • Try the next way, please:

    Sub testAddPictureByIteration()
   Dim obj As OLEObject, Path As String
   Path = ThisWorkbook.Path & "\yourPicture.bmp"
   For Each obj In ActiveSheet.OLEObjects
     If TypeOf obj.Object Is MSForms.Image Then 'to avoid buttons, text boxes etc.
        obj.Object.Picture = LoadPicture(Path)
     End If
End Sub