For loop with image controls on worksheet

[kelly mort]

Hello all
I am issue looping through this code:

For i = 1 To 20 
Sheet1.Controls ("Image" & i).Picture = LoadPicture  ("")
Next i

And I get the error message :

Method or data member not found

What did I do wrongly?

 

[Kenneth Hobson]

Are the objects on the sheet? If so:

Sheet1.OLEObjects("Image" & i).Object.Picture = LoadPicture("")

 

[kelly mort]

Yesssss! ! Thanks for the help.

But I just got myself into a challenge:
I used this code to load the controls

Sheet1.Image1. Picture = LoadPicture (fPath & "\" & Sheet1. Range ("B2") & ".jpg")
Sheet1.Image2. Picture = LoadPicture (fPath & "\" & Sheet1. Range ("B12") & ".jpg")
Sheet1.Image3. Picture = LoadPicture (fPath & "\" & Sheet1. Range ("B22") & ".jpg")
........ to 20 controls

I wanted the error code to deal with only controls with the err 53 yet it happens to be clearing all controls. How do I get over this?

 

[Kenneth Hobson]

Is it due to control or jpg not existing? You can use Dir() to determine if the file exists. e.g.

If Dir(fPath & "\" & Sheet1. Range ("B2") & ".jpg")) <> "" then _
 Sheet1.Image1. Picture = LoadPicture (fPath & "\" & Sheet1. Range ("B2") & ".jpg")

If you have alot of these, you might consider poking the filenames and or control names into array(s). Iterating arrays will then make code easier to maintain.

 

[kelly mort]

Is it due to control or jpg not existing? You can use Dir() to determine if the file exists. e.g.

If Dir(fPath & "\" & Sheet1. Range ("B2") & ".jpg")) <> "" then _
 Sheet1.Image1. Picture = LoadPicture (fPath & "\" & Sheet1. Range ("B2") & ".jpg")

If you have alot of these, you might consider poking the filenames and or control names into array(s). Iterating arrays will then make code easier to maintain.

What is happening is that the range ("B2") may be blank and that will cause the err 53. So I want a way to check and apply the err 53 code to only the controls which have that error.

Oh okay but I have no idea about arrays. Can you assist me here:
Regards

 

[Kenneth Hobson]

IF B2 is "blank" then Dir() will know that the filename does not exit and skip updating the pic.

Change and adapt to suit.

Sub Main()
  Dim i As Integer, fPath As String, a(1 To 2) As String
  
  fPath = Environ$("USERPROFILE") & "\Pictures\"
  
  'Sheet1.[B2] = "city"
  a(1) = fPath & Sheet1.[B2] & ".jpg"
  'Sheet1.[B12] = "water"
  a(2) = fPath & Sheet1.[B12] & ".jpg"
  
  For i = 1 To UBound(a)
    If Dir(a(i)) <> "" Then _
      Sheet1.OLEObjects("Image" & i).Object.Picture = LoadPicture(a(i))
  Next i
End Sub

 

[kelly mort]

The code did not show the images.
Should I uncomment the Sheet1. [B2] = "city" etc?

  fPath = Environ$("USERPROFILE") & "\Pictures\"

What is the above line doing? I don't understand it. Thanks
Kelly

 

[Kenneth Hobson]

a(1) holds the first filename's full path.
a(2) will hold the 2nd.
and so on.

In the Dim a(1 to 2), change 2 to however many elements you want to store. Assign the paths to each element as needed. The loop will never need to be changed.

fPath should be the path to your pics.

You can easily test the code as is in a new file's Sheet1 if you have the city.jpg and water.jpg files in your Pictures folder. Add two Image objects to Sheet1 and run the macro.

 

[kelly mort]

Can I use this path:

fPath = ThisWorkbook.Path & "\" & "Pictures"

 

[Kenneth Hobson]

Yes, it it exists. I like to concatenate the trailing backslash but that is your call.