image vs value in formulas

[lilpapa]

Hey all,

New to the board. I hope you can help.

How can I force Excel to see the contents of a cell, a gif image in this case, as a value?

I have 1 sheet in a workbook to enter the text history of poker hands labeled 'text', another with a horizontal lookup table labeled 'gifs'; first row, the text labels of each card; second row, the gif image of each card.
The third sheet, labeled 'hands', is where I want to see the graphic representation of each hand. I'm using =HLOOKUP(Text!A1,Gifs!$A1:$AZ1,Gifs!2:2) to bring the card image to the sheet 'hands'. But no such luck. It only returns the #VALUE! error.

Have I misused the formula? Does Excel recognize a graphic as a value? Should the gif's be converted to another format? Does Excel even treat a graphic as cell content?

I hope I've made it clear. Many thanks for any help.

George

 

[Barney]

George,
I suggest install a playing cards font. This way the cell will have a value and will work with your look-up formula. I have had a quick look on the net and there are a few shareware playing card fonts available for download .

Barney

 

[Tom Urtis]

How can I force Excel to see the contents of a cell, a gif image in this case, as a value?

Excel cannot "read" a gif or jpg in terms of what the picture shows, but it can identify which picture is which based on that picture object's name and its TopLeftCell property for address, row, or column.

From there it depends on what approach you want to take. In general terms, there are 52 cards in a standard deck, so if you have 52 picture objects, you can set up some kind of Case statement or lookup table based on each picture's address (what cell the top left corner of the object resides in), which you'd set up as part of the workbook design.

Example, this identifies the address of picture 1 on your sheet:
MsgBox ActiveSheet.Shapes("Picture 1").TopLeftCell.Address

So regardless of whether the picture shows a King or Ace or Joker, what the code would really be looking at is the combination of name and cell address (or at least one of those factors, all possible per above example), and you can take it from there as to what action gets taken based on what picture name, address, row, or column is invoked.

 

[PA HS Teacher]

Here is a UDF Damon Ostrander wrote a few months back. It is outstanding, and has been invaluable to me.
http://www.mrexcel.com/board2/viewtopic.php?t=104322&start=10

It does exactly what Toms said is possible. It accepts the address of the address of the picture as text, and places a the desired picture on the sheet to that the upper left corner of the picture aligns with the upper left corner of the cell from which the function is called.

The function is outstanding, to see how I applied it to a student seating chart with pictures, or some other people have applied it, take a look at the post above.

Function ShowPicD(PicFile As String) As Boolean 
'Same as ShowPic except deletes previous picture when picfile changes 
Dim AC As Range 
Static P As Shape 
On Error GoTo Done 
Set AC = Application.Caller 
If PicExists(P) Then 
P.Delete 
Else 
'look for a picture already over cell 
For Each P In ActiveSheet.Shapes 
If P.Type = msoLinkedPicture Then 
If P.Left >= AC.Left And P.Left < AC.Left + AC.Width Then 
If P.Top >= AC.Top And P.Top < AC.Top + AC.Height Then 
P.Delete 
Exit For 
End If 
End If 
End If 
Next P 
End If 
Set P = ActiveSheet.Shapes.AddPicture(PicFile, True, True, AC.Left, AC.Top, 200, 200) 
ShowPicD = True 
Exit Function 
Done: 
ShowPicD = False 
End Function 

Function PicExists(P As Shape) As Boolean 
'Return true if P references an existing shape 
Dim ShapeName As String 
On Error GoTo NoPic 
If P Is Nothing Then GoTo NoPic 
ShapeName = P.Name 
PicExists = True 
NoPic: 
PicExists = False 
End Function

 

[andonny]

Display Picture without the True in cell

Hi,
I am using the ShowPicD function and was wondering if it is possible to not displaying 'True' in the cell. I am displaying a text in the same cell and I always get the True and then the rest of the text.

Your help is ver much appreciated.
Andonny

 

[PA HS Teacher]

Sounds like you want a Sub, not a function

Sub ShowPicD(PicFile As String, AC As Range)
'Same as ShowPic except deletes previous picture when picfile changes
'Dim AC As Range
Static P As Shape
On Error GoTo Done
'Set AC = Application.Caller
If PicExists(P) Then
P.Delete
Else
'look for a picture already over cell
'For Each P In ActiveSheet.Shapes
For Each P In AC.Parent.Shapes
If P.Type = msoLinkedPicture Then
If P.Left >= AC.Left And P.Left < AC.Left + AC.Width Then
If P.Top >= AC.Top And P.Top < AC.Top + AC.Height Then
P.Delete
Exit For
End If
End If
End If
Next P
End If
Set P = AC.Parent.Shapes.AddPicture(PicFile, True, True, AC.Left, AC.Top, 200, 200)
'Set P = ActiveSheet.Shapes.AddPicture(PicFile, True, True, AC.Left, AC.Top, 200, 200)
'ShowPicD = True
Exit Sub
Done:
'ShowPicD = False
End Sub

Function PicExists(P As Shape) As Boolean
'Return true if P references an existing shape
Dim ShapeName As String
On Error GoTo NoPic
If P Is Nothing Then GoTo NoPic
ShapeName = P.Name
PicExists = True
NoPic:
PicExists = False
End Function