How do I insert a picture based on a cell value?

I want to be able to use a drop-down box using validation (I know how to do that) that lists a dozen or so items.  I want to display a picture based on the item selected in the drop-down box.  I have tried several solutions found on the internet and haven't been able to get any to work, however the solutions may not fit my actual needs.
LVL 4
ITworksAsked:
Who is Participating?
 
zorvek (Kevin Jones)ConsultantCommented:
The picture object is typically used to show a graphic image but it can also be used to display a range of cells from another, possibly hidden, section of the workbook. The image displayed is an exact replication of the referenced range including any objects placed over those cells. The reference to a range is created by setting the picture's formula to a range reference. This reference can be a dynamic reference by creating a named formula that, for example, determines a range of cells using the INDEX and MATCH functions to find a range of cells within a larger range of cells containing a table of images to be displayed based on some entered value such as a validation list selection.

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
The code below, when added to a worksheet code module, displays the picture associated with a file name entered in a specific cell. For the code to work, the cell containing the file name must be named "EntryCell" and the range of cells where the picture is to be placed named "ImageArea". The image is fitted into the image area range so that it fits within the range but maintains its proportions.

[Begin Code Segment]

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim Picture As Picture
   
   Const PathToPictures = "C:\Full\Path\To\Pictures\"

   If Not Intersect(Target, [EntryCell]) Is Nothing Then
      On Error Resume Next
      Shapes("Picture" & [EntryCell].Address(False, False)).Delete
      On Error GoTo 0
      If Len([EntryCell]) > 0 Then
         If Len(Dir(PathToPictures & [EntryCell] & ".jpg")) > 0 Then
            Application.ScreenUpdating = False
            Set Picture = Sheets("Sheet1").Pictures.Insert(PathToPictures & [EntryCell] & ".jpg")
            Picture.Name = "Picture" & [EntryCell].Address(False, False)
            Picture.Top = [ImageArea].Top
            Picture.Left = [ImageArea].Left
            Picture.Width = Picture.Width * [ImageArea].Height / Picture.Height
            Picture.Height = [ImageArea].Height
            Application.ScreenUpdating = True
         End If
      End If
   End If

End Sub

[End Code Segment]

Kevin
0
 
Saqib Husain, SyedEngineerCommented:
Here is a beautiful solution from Rory (although not accepted as a solution) which seems to be appropriate for your case. You might browse other solutions in this thread.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26863288.html#35055000

Saqib
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
ITworksAuthor Commented:
ssagibh:  I can’t get the same results.  His demo is showing clip art….not a true picture.  Do you think that is why?

Kevin:  I'll be trying your suggestion today.  
0
 
ITworksAuthor Commented:
I'm not getting the results I need.  Would you please open up my attached spreadsheet and respond to what I need?  

Thanks so much for your help. Sue-Test-Pic.xlsx
0
 
zorvek (Kevin Jones)ConsultantCommented:
You didn't insert a picture control to hold the picture.

See attached.

Kevin
Sue-Test-Pic.xlsx
0
 
ITworksAuthor Commented:
Everything is working great.....but I have NO idea how you did this and I would love to know how to do it.  You said I forgot to insert a picture control to hold the picture.  Yep...you betcha.....because I have no idea what a picture control is.

So it looks like you named a range PictureList on the sheet that holds my pictures as well as the name of them on the left hand side.  

Now can you please explain what you did on the other sheet for this to work.  I don't see any VBA code.....what's the secret?

0
 
ITworksAuthor Commented:
Thanks, Kevin!   I wish I had one tenth of your knowledge.
0
 
ITworksAuthor Commented:
Darnit, I still don't understand!  

 OK....so......step 1....

1. Insert pictures into sheet labled picsheet.
2.Resize pictures to fit into one cell
3. Label the pictures accordingly. 1, 2, 3 or Penguins, Ubolt, etc.
4. Select both the pictures and the labels and name this range "PictureList"
5. Go to sheet you want to display picture.
???????

Please see my form.  I want to insert a picture at the bottom right corner but again, i don't know how to do it!
Sorry ssaqibh.....didn't mean to discredit you.  
Router---Sue.xlsm
0
 
zorvek (Kevin Jones)ConsultantCommented:
Close...the problem now is the named formula - PictureReference.

Open the name manager and delete the first reference as it was created by Excel when you made a copy of the worksheet.

Select the second reference and change the formula from:

   =INDEX(PicSheet!$B:$B,MATCH(Router!$A$39,PicSheet!$A:$A,FALSE))

to:

   =INDEX(PicSheet!$B:$B,MATCH(Router!$A$1,PicSheet!$A:$A,FALSE))

Now, when you change Router!A1 the picture will change.

Kevin
0
 
Saqib Husain, SyedEngineerCommented:
Sorry, modguy, but that was supposed to be an objection. It was my idea and the OP likes it but does not consider sharing points. Do I not have a right to at least protest?
0
 
ITworksAuthor Commented:
With that said, we awarded points not because they offered an identical solution, but because they went above and beyond with helping on the Excel spreadsheet.
0
 
Saqib Husain, SyedEngineerCommented:
How about a sweet little thanks....which I have yet to see... for a solution which was not there until I proposed it.
0
 
ITworksAuthor Commented:
Thank you for proposing somebody else's EE solution.  Your ability to perform a search was invaluable.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.