Limit on Pictures?

[Dav1d]

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

 

[John]

Hi David
I posted basically the same thing yesterday and today at MS.excel.Programming
but no help.
I get exactly 64 Picture before it start to no longer work.
Hoping someone can help us !!!!
Here is my code, modified from http://mcgimpsey.com/excel/lookuppics.html

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oPic As Picture
Me.Pictures.Visible = False
For Each oPic In Me.Pictures
If oPic.Name = ActiveCell.Text Then
oPic.Visible = True
oPic.Top = 100
oPic.Left = 600
If ActiveCell.Cells > Range("B31") Then
oPic.Top = 250

If ActiveCell.Cells > Range("B41") Then
oPic.Top = 380

If ActiveCell.Cells > Range("B52") Then
oPic.Top = 550

If ActiveCell.Cells > Range("D2") Then
oPic.Top = 100
Exit For
End If
End If
End If
End If
End If
Next oPic
End Sub
**********************************
Regards
John

 

[Dave Peterson]

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.

 

[John]

Hi Dave
Yes the problem is on that line. Will try out your suggestion and get back to
you.
Thanks
John

 

[John]

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

Hi Dave
Yes the problem is on that line. Will try out your suggestion and get back to
you.
Thanks
John

 

[Dave Peterson]

I don't understand the question.

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

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]

Hi Dave
Thanks for your help. Let see if I can make this more clear.
I've got a list of names ( DVD Movies ) starting at cell B3 going down to B62
and D3 to D62.
When I select a cell, the Picture appears to the top right of my list, but has I
go down the list the pictures are staying at the top. I would like the Picture
to scroll down the list. Presently If I select cell B50, I have to scroll up to
the top to see the picture.

I tried this, see below:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oPic As Picture
Me.Pictures.Visible = False
For Each oPic In Me.Pictures
If oPic.Name = ActiveCell.Text Then
oPic.Visible = True
" This line not working >>>" oPic.Top = ActiveCell.Offset(3, 3) '(No error)'
oPic.Left = 600
Exit For
End If
Next oPic
End Sub
---------------------------------------------------------------------
Modified with your help, solving the limit of picture it could handle.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oPic As Picture
For Each oPic In Me.Pictures
oPic.Visible = False
Next oPic
For Each oPic In Me.Pictures
If oPic.Name = ActiveCell.Text Then
oPic.Visible = True
oPic.Top = 100
oPic.Left = 600'>Trying to make the picture go down the sheet and would need about 5 or 6 "IF"
.. Don't like this but can't figure anything else.
oPic.Top = 250
oPic.Left = 600
Else
oPic.Visible = False
End IF
End IF
'Next oPic
End Sub

Hope you have something up your sleeve that can solve my problem.
Regards
John

 

[Dav1d]

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]

Instead of this:
oPic.Top = ActiveCell.Offset(3, 3) '(No error)

You could try
oPic.Top = target.top

But if you like to see that picture when you're scrolling (not changing
selection--just scrolling using the scrollbars), you could put the pictures in
row 1 and freeze the panes so that row 1 is always visible.

===============
Another approach that I would use...

Put the pictures where you want them. Then just hide/show them. Don't change
the location--just change the .visible property to true.

 

[Dave Peterson]

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]

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.

 

[John]

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

 

[John]

Hi Dave
You're "Great" nothing less, I'm using > oPic.Top = Target.Top / 1.5
and it works perfectly.
I see you have other ideas for my problem, I'll keep them incase problems come
up in the future
Thanks again
John

 

[Dave Peterson]

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.