DISPLAY Image Based on Logic

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to display a number of corporate logos on a report I am creating. The
report(s) run from a single template, so I want to be able to DISPLAY (not
hyperlink) the appropriate logo, based on a cell's content.

Is this even possible?

Thanks!
Ray
 
Try this

Add a module in excel:



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



To use this, you will type this in the cell:

=ShowPicD()



You place the location of the file in the ( ).
 
I need to display a number of corporate logos on a report I am creating. The
report(s) run from a single template, so I want to be able to DISPLAY (not
hyperlink) the appropriate logo, based on a cell's content.

Is this even possible?
Click to expand...

Yes, but it's fragile, i.e., easily broken/screwed up. You could create a
table with wider than usual columns and taller than usual rows. Enter
distinct identifying strings in the first column and insert the logo images
ENTIRELY inside the cells in the second column. Name that table (spanning the
two columns and all its rows) LTBL.

Then in the worksheet where you want the logos to appear, copy a cell, hold
down a [Shift] key and click on Edit in the menu, then click on Paste Picture
Link. With the picture link still selected, change its formula to =LOGO1. If
the cell that gives the identifier for the first logo were X99, define the
name LOGO1 as

=INDEX(LTBL,MATCH($X$99,INDEX(LTBL,0,1),0),2)

The picture link should now appear as the logo corresponding to the value of
X99.

You could copy and paste the picture link repeatedly and place the copies
wherever you want them. Then change them to refer to different defined names:
LOGO2, LOGO3, LOGO4, etc. Then define these names using the formula above but
changing $X$99 to the appropriate cell containing the identifier.

YOU have to ensure that YOU use the same identifying text in LTBL that
already appears in your display worksheet.
 
John,

This looks like it might help, but I have a few questions and at least one
problem...

1) I assume that the location to be entered into =ShowPicD() would be the
cell reference where an image is "floating", such as =ShowPicD($A$2) correct?

2) Assuming I have that right, can I nest a vlookup or something else in
there, so as to be able to "pull" the image I want?

3) The line that starts with, "Set P = ActiveSheet.Shapes.AddPicture" bombed
out until I wrapped "AC.Top, 200, 200)" into the previous row - is that
correct?

4) With a cell reference in place, as described above, the formula returns
FALSE.

Sorry... I'm not really a programmer... I just do my best to reverse
engineer working code!

I can see this solution has good potential for my needs... but I am
apparently missing something. I really appreciate your help!

Later-
Ray
 
Built the table - no problem

Col. A has a three char. code
Col. B has the logo (insert picture) completely inside the cells
Named Range is LTBL for the entire table
With the picture link still selected, change its formula to =LOGO1
Click to expand...
Got to this step and may have misunderstood...
I did enter, literally =LOGO1 in the formula window of the selected pic.
When this didn't work, I also changed it to the three char code from the
table, but that didn't work either...
define the name LOGO1 as
=INDEX(LTBL,MATCH($X$99,INDEX(LTBL,0,1),0),2)
Click to expand...
Not sure what you mean by define the name "LOGO1"
I did change the reference to $A$2, where my first image appears

What have I missed...?

Thanks!
Ray



Harlan Grove said:
I need to display a number of corporate logos on a report I am creating. The
report(s) run from a single template, so I want to be able to DISPLAY (not
hyperlink) the appropriate logo, based on a cell's content.

Is this even possible?
Click to expand...

Yes, but it's fragile, i.e., easily broken/screwed up. You could create a
table with wider than usual columns and taller than usual rows. Enter
distinct identifying strings in the first column and insert the logo images
ENTIRELY inside the cells in the second column. Name that table (spanning the
two columns and all its rows) LTBL.

Then in the worksheet where you want the logos to appear, copy a cell, hold
down a [Shift] key and click on Edit in the menu, then click on Paste Picture
Link. With the picture link still selected, change its formula to =LOGO1. If
the cell that gives the identifier for the first logo were X99, define the
name LOGO1 as

=INDEX(LTBL,MATCH($X$99,INDEX(LTBL,0,1),0),2)

The picture link should now appear as the logo corresponding to the value of
X99.

You could copy and paste the picture link repeatedly and place the copies
wherever you want them. Then change them to refer to different defined names:
LOGO2, LOGO3, LOGO4, etc. Then define these names using the formula above but
changing $X$99 to the appropriate cell containing the identifier.

YOU have to ensure that YOU use the same identifying text in LTBL that
already appears in your display worksheet.
Click to expand...
 
Back
Top