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
Sue TaylorProject ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Sue TaylorProject ManagerAuthor 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
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Sue TaylorProject ManagerAuthor 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
Sue TaylorProject ManagerAuthor 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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sue TaylorProject ManagerAuthor Commented:
Thanks, Kevin!   I wish I had one tenth of your knowledge.
0
Sue TaylorProject ManagerAuthor 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
Sue TaylorProject ManagerAuthor 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
Sue TaylorProject ManagerAuthor Commented:
Thank you for proposing somebody else's EE solution.  Your ability to perform a search was invaluable.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.