Show picture depending on cell content

[Darkzler]

Greetings everyone,

I've searched the forums, tried suggested solutions, to display a picture based on a specific cell value but failed so far.

In my case I want to display a trophy (from Playstation 4) and there are 4 different types called:

- Platinum
- Gold
- Silver
- Bronze

Column A will contain the name, trophy type, i.e. Platinum/Gold/Silver/Bronze
Column B will contain the actual icon/picture, the problem is:

How can I display a picture in column B depending on the value in column A on Sheet 1.

I've added the pictures in Sheet 2, A1-A4 with their names in B1-B4.

Please help me out guys and please be thorough in your directions since I'm new to Excel.

Best regards and thank you in advance,

Darkzler

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

Here is one way.

First, I decided not to use the names on Sheet2. ALso, using this method, it does not matter which cells the pictures are in, either.

However, you do need to name the pictures.
Select a picture.
You should now see the picture's name in the name box at the top left hand side of the Excel window. It probably says "Picture 1".
Overtype that with the name of the picture e.g. Gold and hit Enter.
Repeat for the other pictures.

As a check, just re-select each picture in turn and make sure the right name appears in the name box.

If everything is OK then paste the code below into the Sheet1 macro module.
(Go into the VB Editor, see where it says Sheet1(Sheet1) in the project window. Double-click and paste into the editor window on the right.)

Now, if you go to Sheet1 and type one of your names into column A the picture should appear in column B.

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo Error
    If Target.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    If Not Intersect(Target, Columns(1)) Is Nothing Then
        ThisWorkbook.Worksheets("Sheet2").Shapes(Target.Value).Copy
        Application.Goto Target.Offset(0, 1)
        Paste
    End If
    
Error:
    Application.EnableEvents = True
    
End Sub

How does the code work?

Worksheets have Events. When something happens on the sheet an Event can sometime be triggered. In this case it is detecting a change to the data on the sheet - hence Worksheet_Change.

If Not Intersect(Target, Columns(1)) Is Nothing Then

is detecting if a change has been made in column A.
If it has then it runs the Copy and Paste code in between the If and End If.

If Target.Count > 1 Then Exit Sub

stops the code from running if more than one cell has been selected.
The On Error and EnableEvents codes are to prevent the risk of problems.

Application.Goto Target.Offset(0, 1)

This code goes to Sheet1 to a cell one column to the right or the Target cell i.e. column B but in the same row as where the change happened

 

[Darkzler]

Hi RickXL,

Wow, thanks alot! It worked like a charm and on first try. This is exactly what I was hoping for and much appriciated that you actually explain what the code does. Perfect example for me to learn from.

I wish you a good day Rick and once again, thank you!

 

[RickXL]

No problem and thanks for the feedback.

I am pleased it is working as you wanted.

Regards,