Gord Dibben wrote:
Steve

Beats me<g

JE may drop by and give some insight......hint, hint, nudge, nudge.


Gord

On 1 Dec 2006 07:19:55 -0800, "Steve" wrote:

This now is very weird. I copied the two sheets that are in my master
file off to a new document. My main document is 21 meg and this new
document has no data in it; just the format and graphs that don't link
anywhere. And now the routine works just fine. So there could be some
relationship to the size of the file. I am going to try it the other
way where I eliminate all the sheets and see if it begins to work.

Have you ever heard of anything like this?

Gord Dibben MS Excel MVP


Hi Steve and Gord,

I've encountered a few other instances where projects using this code
have failed because of the same problem.
In each case, they have added the code and it works well, then they
have added more pictures to the sheet only to discover that a point is
reached when the code falls down at...

Me.Pictures.Visible = False

I'm guessing Steve hit the problem straight away because his
worksheet already had enough pictures to cause the problem when the
code was added.

I've been lucky enough to find a solution but I'm not smart enough
to figure out why the problem occurs in the first place.

My solution to the problem has been to avoid dimensioning the pictures
as Picture objects.
I have dimensioned them as Shape objects and then separated the
pictures from all of the other shapes using their Type property, which
is msoPicture or 13.

After making the above change, as far as I can tell, it is then no
longer possible to make all the pictures invisible in one fell swoop
the way that "Me.Pictures.Visible = False" does, so a loop must be
used in its place.

The following code should work for Steve after he either edits the code
so that it contains the name that Excel uses for his Logo or he changes
his logo's name (in the Name box on the left side of the Formula bar)
to "Logo"...

Private Sub Worksheet_Calculate()
Dim oPic As Shape
Application.ScreenUpdating = False
For Each oPic In Me.Shapes
If oPic.Type = 13 And oPic.Name < "Logo" Then
oPic.Visible = False
End If
Next oPic
With Range("N7")
For Each oPic In Me.Shapes
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub

Note that on his site JE adds the following warning...
"Note that for large numbers of pictures (like, say, a parts
catalog), a database solution would likely be much easier and much more
efficient."

Ken Johnson