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 ( ).


--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"RayportingMonkey" wrote:

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

"RayportingMonkey" wrote...
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?

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.

To have a picture show up when you select an item from a DV list or type in a
name see JE MCGimpsey's site.

http://www.mcgimpsey.com/excel/lookuppics.html

As written John's code allows for only one picture to be displayed.

Got to Debra Dalgeish's site for a sample workbook from Bernie Dietrick for
adding pictures to selections.

Alows for more than one picture to be displayed.

http://www.contextures.on.ca/excelfiles.html#DataVal

DV0049 - ClipArt Selection


Gord Dibben MS Excel MVP

On Tue, 24 Jul 2007 13:02:02 -0700, RayportingMonkey
wrote:

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

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

"John Bundy" wrote:

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 ( ).


--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"RayportingMonkey" wrote:

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

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
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)
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" wrote:

"RayportingMonkey" wrote...
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?

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.