Displaying large number of pictures in EXCEL

[ravi.palakodeti]

Hi:

I have developed a small application which, beside other things,
displays pictures in the work sheet based on a dropdown list value. I
have previously used the following code from mcgimspey associates for
this purpose and it worked great.

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("A1")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub

Now, I have about 1200 pictures from which I want to choose the
picture I want to display based on a cell value. I tried the above
code but I get an error "Runtime error 1004: Application-defined or
object defined error". The correct picture is selected and moved to
the correct range, but the other pictures are not invisible, as
written in the code. On other variations of the code, sometimes I get
the error "Unable to set object property" or "out of memory".

I have tried another approach, that of using an external folder to
load the pics. It gives no compiling errors but doesn't display any
pictures either. Here is the code for this:

Sub test()
Dim s As String, i As Integer

s = "C:\Pics"


ActiveSheet.Range("A1").Select
With Application.FileSearch
.NewSearch
.LookIn = s
.SearchSubFolders = False
.Filename = ActiveSheet.Range("A1")

'.MatchTextExactly = True
.Execute
For i = 1 To .FoundFiles.Count
ActiveSheet.DrawingObjects.Delete
ActiveSheet.Pictures.Insert (.FoundFiles(i))
Exit For
Next i
End With
End Sub

This code hasn't worked for a test case scenario either.
Each of the pictures is about 60 kb in size in jpg format. I'd really
appreciate it if anyone can suggest what I may be doing wrong or
overlooking. Perhaps a code which can handle large number of pictures
is what I need.

TIA,
Ravi

 

[Dave Peterson]

What's the line that causes the error?

Is it this one:

Me.Pictures.Visible = False

if yes, maybe you could replace it with a loop:

for each opic in me.pictures
opic.visible = false
next opic

If that's not the line, you may want to share what is.

Or maybe you could use this instead:

Private Sub Worksheet_Calculate()
Dim oPic As Picture

With Range("A1")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
else
opic.visible = false
End If
Next oPic
End With
End Sub