jpegs in cells

[Guest]

I have been setting up a spreadsheet to use pictures in cells depending on
the value of a cell. I had a minor problem initially with the set up, but
thanks to JLatham we solved the problem. The trouble I am experiencing now
is that the VBA command appears to be jamming up. I have been using the
setup found at McGimpsey & Associates for "Looking up and displaying a
picture based on a cell value" for the cell formulas and the VBA. I have
expanded the sample found at McGimpsey from 4 pictures to 44 pictures. The
McGimpsey sample will show all pictures in one cell. I am spreading mine
over 44 cells. I have repeated the VBA command to reference the cells I want
the pictures to appear in. All has been working fine for the last ~30
pictures. Now I am suddenly running into a problem with the VBA command
Me.Pictures.Visible = False. I have been trying to enter picture ~30 and an
error message comes up asking me to "End" or "Debug". In addition to this,
the spreadsheet locks up. It does not seem to matter if I attempt to Debug
or End. Even when I try to close the spreadsheet it takes a long time. I
need to reboot my computer because Excel will jam even without a spreadsheet
open. I have to use the Task Manager to close Excel. Any ideas? Have I
exceeded a limit to the number of pictures that I can enter? Am I
overlooking a cell reference? Please let me know
David

 

[Guest]

Could be any number of issues.

How large are the jpegs you're using? How much RAM does your computer have?
What version of XL are you using? What's your processor speed?

Older versions of excel or computers with limited memory may not handle many
images well. If none of these questions are relevant (you have an up to date
computer with sufficient RAM and a recent version of XL) then you probably
need to debug your VBA code. If that's the case, it would help if you posted
the code that is giving you problems.

Dave

 

[Guest]

The size of the pictures should not be too much of a problem. These are
small icons, 20kb? As for the version of Excel, I have Excel 2003. Is this
too old? I do not kow how much RAM I have. The computer is a Dell Pentium
M. As for the VBA, it is the third line that is giving me a problem. It
won't make the picture invisible. When I hit the Enter button, the VBA is
activated and that is when the Debugger comes on.
Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("B14")
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
With Range("B15")
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
....

Does this information help you?
David

 

[Ken Johnson]

Hi David,

I've seen this problem before and I've managed to get it working again
by dimensioning the pictures as Shape objects rather than Picture
objects. I have no idea as to why the problem occurs nor why the change
in dimensioning should be a cure. With that in mind, I can't guarantee
that this solution will work in your case as it has in others.

In changing "Dim oPic As Picture" to "Dim shpPic As Shape", a
consideration that then needs to be made is that there are a lot of
shape objects that could be on your sheet and you wouldn't want the
code to have any effect on their visibility, eg the little drop down
arrow that indicates a cell with a drop down list is a Shape object,
and you won't want them disappearing on you!
So, to ensure that these other shapes are left alone, the code loop
first checks the Shape's Type property. If the Shape's Type is
included in the comma separated list of values in the Select Case then
it will become invisible if its name is not in the range "B14:B113" (I
have assumed that 100 cells starting from B14 will accommodate your
list of picture names)

If you're not sure of a Shape's Type value, then one way of finding it
is to select that shape, then in the Immediate Window of the Visual
Basic Editor type...

?Selection.ShapeRange.Type

then when you press Enter you should see its Type value appear.
Some examples of Shape Type values are...

AutoShapes = 1
Freeform = 5
Grouped Shapes = 6
Drop Down = 8
Line = 9
Picture = 13
TextBox = 17

I'm guessing, from the code you posted, that you have repeated the same
code steps for each picture. I have instead used two,nested loops.

One other change I made to the original code was to include the
possibility of having shapes whose type is included in the Select Case
but are still ignored because their Name starts with the Tilde
character "~". This means that if there is a particular picture (Type =
13) that you want to always be visible, then you can edit its name in
the Name box on the right of the Formula Bar so that the first
character is the tilde, eg if picture named Dog has its name changed to
~Dog then it will be ignored by the code and will always be visible.

I've also removed case sensitivity for the picture name, so that DOG,
dog, Dog etc will all show a picture whose name is Dog if that name is
in the B14:B113 cells


Private Sub Worksheet_Calculate()
Dim blnShow As Boolean
Dim sngTop As Single
Dim sngLeft As Single
Dim shpPic As Shape
Dim rngPicCell As Range
Dim rngPicRange As Range
Set rngPicRange = Me.Range("B14:B113")
'The above range allows for 100 cells
'for holding Pic names. Edit to suit
'your needs.
For Each shpPic In Me.Shapes
If Left(shpPic.Name, 1) <> "~" Then
'if any shapes on your sheet need to be
'unaffected by the code then you will
'need to preface their name with the
'Tilde character. For example, say there
'is a Pic named "Logo"; if the text "Logo"
'is not in any of the cells in rngPicRange,
'then the code will render the Logo shape
'invisible. However, if it is renamed
'"~Logo" it will reman visible at all times.
For Each rngPicCell In rngPicRange
Select Case shpPic.Type
Case 1, 5, 6, 13
'only AutoShapes (1), Freeforms(5),
'Grouped shapes (6), and pictures (13)
'will have their visibility 'controlled.
'Edit to suit your needs
If UCase(rngPicCell.Value) = _
UCase(shpPic.Name) Then
blnShow = True
sngTop = rngPicCell.Top
sngLeft = rngPicCell.Offset(0, 1).Left
Exit For
End If
End Select
Next rngPicCell
If blnShow Then
With shpPic
.Visible = True
.Left = sngLeft
.Top = sngTop
End With
Else: shpPic.Visible = False
End If
blnShow = False
End If
Next shpPic
End Sub


Ken Johnson

 

[Guest]

Thanks Ken. I appreciate your reply, but I got to say your talking way over
my head. It took ma a couple of days of picking and manipulating the sample
I referenced earlier to learn how to do this in the first place.

I understand your point about not wanting the other shapes to disappear. I
am not certain as to the "comma separated list of values". I am using a
Picture Table with the name of the picture in a cell and the picture number
in the cell beside it. I beleive this is the equivallent to the range
"B14:B113" you make below, correct? Also, from your directions I get the
impression that I need to paste the pictures/shapes into specific cells,
correct? If this is the case, I have not done this with the existing
pictures. I was under the impression that if I pasted the picture anywhere
on the sheet, that was sufficient. Do I need to select a range of cells and
repaste all the pictures in these cells?

I do not know how to check the "Shape's Type Value" for the pictures. Once
pasted the picture on the sheet I entered the picture number in the cell
beside the name I wanted it referenced by in the Picture Table, the picture
would disappear when I hit enter. I do not know how to get them to be
visible again.

I would need guideance to see the shape values you have listed below.

I can follow what you have done with regards to the Tilde. I do not have a
picture that I need visible at all times, but I will keep this in mind for
future applications. Also I am not worried about case sensitivity as it is
the value of an IF statement that triggers a cell to show the name of the
picture. However this will also be usefull for future applications and I
appreciate this also.

As for the formula you have provided the line "Set rngPicRange =
Me.Range("B14:B113")", since I am using a Picture Table similar to the sample
referenced in earlier postings, should it appear as "Set rngPicTab =
Me.PicTab1" I have created 4 Picture Tables and titled them PicTab1 to
PicTab4. I am guessing that I will have to repeat this section of the
formula for each Picture table, correct?

David

 

[Ken Johnson]

Hi David,

I am not certain as to the "comma separated list of values".

If all of your Pictures are just that, ie pictures (JPEGs, TIFFs etc)
then the only number you need after the Select Case is 13.

I am using a Picture Table with the name of the picture in a cell and the
picture number in the cell beside it. I beleive this is the equivallent to the
range "B14:B113" you make below, correct?

I was guessing that your list of Picture names simply ran down column B
for 44 picture names but I just made room for 100. I'm now guessing
this is not the case since you mention 4 Picture tables.
The code is easily changed to accommodate any number of Picture tables
in any number of different columns (see later).

Also, from your directions I get the impression that I need to paste the
pictures/shapes into specific cells, correct?

No, as with your previous code, the pictures can be pasted anywhere on
the sheet. The code either hides a picture or moves it to the cell with
its name.

the picture would disappear when I hit enter. I do not know how to get them
to be visible again.

Annoying isn't it! This is inevitable during the development stage.
Not to worry, just run this code snippet to get them all visible
again...

Public Sub Show_All()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
shp.Visible = True
Next shp
End Sub

I would need guideance to see the shape values you have listed below.

You probably won't ever need to use this because you're "icons"
are probably all pictures and their Type number is probably 13.
However, if you have a different type of shape then one way of
determining its Type number is to...

1. Select it.
2. Press Alt + F11 to get into the Visual Basic Editor.
3. Either Go Ctrl + G or View|Immediate Window to open up the
Immediate Window.
4. In the Immediate Window type...

?Selection.ShapeRange.Type

Then press Enter and you should see a number appear just below the line
of code you typed. That is the Type number of the currently selected
Shape.

As for the formula you have provided the line
"Set rngPicRange = Me.Range("B14:B113")",
since I am using a Picture Table similar to the sample referenced in
earlier postings, should it appear as
"Set rngPicTab = Me.PicTab1"

I don't think that matters, tables are ranges.

You will however need to change the address in...

"Set rngPicRange =Me.Range("B14:B113")"

You mention 4 picture tables.
Say the cells used by these tables for holding (and changing) the
picture names are B14:B24, E14:E24, H14:H24 and K14:K24. These are then
the only cells that the code needs to examine for it to determine each
picture's visibility and position. The "Set rngPicRange = Me.etc"
should be changed to...

Set rngPicRange = Me.Range("B14:B24,E14:E24,H14:H24,K14:K24")

If you have any trouble making this change just let me know.

I hope this makes sense.

Let me know how it all goes.

Ken Johnson