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?

 

[kelly mort]

If you have a copy of your test file can you let me have it ? I am a bit lost here
Kelly

 

[Kenneth Hobson]

I added a few more features.

https://www.dropbox.com/s/qgp932zuqy37nmd/LoadPicturesFromPicturesFolder.xlsm?dl=0

Sub Main()
  Dim i As Integer, fPath As String
  Dim a(1 To 2) As String 'Change 2 to number of array elements.
  
  'Speed up settings.
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual
  Application.ScreenUpdating = False
  
  fPath = Environ$("USERPROFILE") & "\Pictures\"
  
  'Exit if fPath does not exist.
  If Dir(fPath, vbDirectory) = "" Then
    MsgBox "Folder does not exist:" & vbLf, vbCritical, "Macro Ending"
    Exit Sub
  End If
  
  'Add pic names to cells for testing purposes.
  'P1Names
  P2Names
  
  'Add elements to array.
  a(1) = fPath & Sheet1.[B2] & ".jpg"
  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
  
  'Restore settings.
  Application.EnableEvents = True
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub

'Add pic names to cells for testing purposes.
Sub P1Names()
  Sheet1.[B2] = "city"
  Sheet1.[B12] = "water"
End Sub

'Add pic names to cells for testing purposes.
Sub P2Names()
  Sheet1.[B2] = "night"
  Sheet1.[B12] = "sunset"
End Sub

 

[kelly mort]

I added a few more features.

https://www.dropbox.com/s/qgp932zuqy37nmd/LoadPicturesFromPicturesFolder.xlsm?dl=0

Sub Main()
  Dim i As Integer, fPath As String
  Dim a(1 To 2) As String 'Change 2 to number of array elements.
  
  'Speed up settings.
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual
  Application.ScreenUpdating = False
  
  fPath = Environ$("USERPROFILE") & "\Pictures\"
  
  'Exit if fPath does not exist.
  If Dir(fPath, vbDirectory) = "" Then
    MsgBox "Folder does not exist:" & vbLf, vbCritical, "Macro Ending"
    Exit Sub
  End If
  
  'Add pic names to cells for testing purposes.
  'P1Names
  P2Names
  
  'Add elements to array.
  a(1) = fPath & Sheet1.[B2] & ".jpg"
  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
  
  'Restore settings.
  Application.EnableEvents = True
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub

'Add pic names to cells for testing purposes.
Sub P1Names()
  Sheet1.[B2] = "city"
  Sheet1.[B12] = "water"
End Sub

'Add pic names to cells for testing purposes.
Sub P2Names()
  Sheet1.[B2] = "night"
  Sheet1.[B12] = "sunset"
End Sub

Okay. But the issue is the cells B2 , B12 etc are filled by a former. They are not constant. I am using combobox to change sheets in the userform and then the names in those cells will change as well.

 

[Kenneth Hobson]

That does not matter. I just illustrated the concept.

If it were me, I would use the Change event and act accordingly. That change can be by Combobox, cell, etc. It is up to you to decide what best meets your needs.