Since you set up the pictures/grouping, maybe you could avoid the problem by
having the code avoid the names you want to ignore.

with range("b5")
For each oPic in Me.Pictures
select case lcase(opic.name)
'make sure all are in lower case!
case is = "pic1", "pic2", "pic3"
'do nothing, leave them as-is (hidden or showing)
case is = .Text 'still in the With Range construct
opic.visible = true
case else
opic.visible = false
end select
next opic
End with

I'm not sure if you have the "exit for" in your loop or not--I'm getting my
threads confused!

And I haven't worked with that many grouped pictures, so I'm not sure this is
the easiest way--but I do know that it's what I'd try first (it seems the
simplest to me -- until someone shows a better way <vbg.

I didn't test or compile that code--so watch out for typos.


Dav1d wrote:

Hi Dave
Sorry, I wasn't very clear. The original set up didn't hide the 'fuel
gauge' picture because it saw it as a group rather than a picture. The
images that the code controlled sat nicely on top of the guage image giving
the appearance of a needle moving.

With the revised code it now recognises the 'gauge group' as containing a
picture, so it upgroups it and hides the image with the other pictures.

"Dave Peterson" wrote:

If you've grouped the pictures, you may find it easier going through the Shapes
collection.

But be careful, Shapes includes lots of things (like data|validation dropdown
arrows!), so you'll want to be careful.

I'd start by reviewing Ron de Bruin's site:
http://www.rondebruin.nl/controlsobjectsworksheet.htm



Dav1d wrote:

Hi Dave
This worked perfectly, thank you.

One thing that puzzles me. I was using this code to drive a 'fuel gauge'
chart, the pictures being the needle. With the original code the 'fuel
gauge' picture remained visible because I turned it into a group rather than
a pic. The new code uncouples the group and hides the gauge. Perplexing. I
continue to work on this challenge but if you have any idea of a fix I'd
surely welcome it.

Thanks once again.

"Dave Peterson" wrote:

Which line causes the error?

If it is:
Me.Pictures.Visible = False

Then replace that line with this block of lines:

for each oPic in Me.Pictures
opic.visible = false
next opic

You could actually combine this block with the other for/each block:

Option Explicit
Private Sub Worksheet_Calculate()
Dim oPic As Picture
With Range("b5")
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

Notice the "exit for" line is gone--so that the code can hide all the pictures.




Dav1d wrote:

I've built a picture display in excel using the (widely recommended) link:

http://mcgimpsey.com/excel/lookuppics.html

It works superbly! But once I reach 60 pictures it generates a 1004 runtime
error: "Unable to set the Visible property of the Pictures class".

The code - supplied on the mcgimpsey site - doesn't suggest any limitations
so I'm wondering if its an excel limit. Can anyone advise? I've copied the
code below.

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("b5")
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

Once again, the mcgimpsey solution is first class, the problem is getting to
the target 110 pictures.

Many thanks
David

--

Dave Peterson
.


--

Dave Peterson
.


--

Dave Peterson