Limit on Pictures?
 

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

Limit on Pictures?
 

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

Limit on Pictures?
 

Hi Dave
Yes the problem is on that line. Will try out your suggestion and get back to
you.
Thanks
John
"Dave Peterson" wrote in message
...
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

Limit on Pictures?
 

Hi Dave
I put in 102 Pictures and "Visible = False" works.
I need to name every picture then test it out again. May not have enough time
this evening, but it looks
like one problem is solved.
Dave, any idea how to make the picture scroll down with the list, my code is
different from David.
Thanks again
John
"John" wrote in message
...
Hi Dave
Yes the problem is on that line. Will try out your suggestion and get back to
you.
Thanks
John
"Dave Peterson" wrote in message
...
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

Limit on Pictures?
 

I don't understand the question.

You could either restate your question or post your current code -- or both!

John wrote:

Hi Dave
I put in 102 Pictures and "Visible = False" works.
I need to name every picture then test it out again. May not have enough time
this evening, but it looks
like one problem is solved.
Dave, any idea how to make the picture scroll down with the list, my code is
different from David.
Thanks again
John
"John" wrote in message
...
Hi Dave
Yes the problem is on that line. Will try out your suggestion and get back to
you.
Thanks
John
"Dave Peterson" wrote in message
...
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

Limit on Pictures?
 

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
.

Limit on Pictures?
 

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

Limit on Pictures?
 

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
.

Limit on Pictures?
 

Hi David
Please accept my apologies for SideTracking your post. Dave Peterson has been a
great help and I need to thank you also.
Without your post, I'd still be in hot water.
Regards
John
"Dav1d" wrote in message
...
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
.

Limit on Pictures?
 

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