Swap images depending on text in cell (vlookup for images??)

Hi all,

I'm trying to get logos to appear around F12 in the Contract Select tab depending on the text (contract) selected in the drop down list - C13 on the same page.

[See attached - I have one of the logos in the Contracts tab and I'll be places the others next to the other options]

sample.xlsx


Is there a vlookup that works on images? I've never done anything like this before..

Really appreciate any input!

Many thanks
Leo

Here is a macro-based example. You can adapt it to your needs.
Make your selection in cell C11. Macros must be enabled for this to work!

And a solution using formulas is here.

http://www.youtube.com/watch?v=CEM0pp5SjMU

Fotis1991:

A very nice demonstration!

Originally Posted by Jakobshavn

Fotis1991:

A very nice demonstration!

.........................

Hi ld2x07:

Perhaps you could adopt the technique posted by Fotis1991.

Hi Leo

You can't achieve it with just a Vlookup function, although you would need that to find the correct image.
Provided you can accept a VBA solution, take a look at the attached workbook.

I inserted a new column B to your sheet Contracts, and enter the Image name alongside the Client name

Then I inserted some event code on sheet Contact Select, as follows

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim image As String
    If Target.Count > 1 Then Exit Sub
    If Target.Address <> "$C$13" Then Exit Sub
    Me.Shapes(1).Delete
    image = WorksheetFunction.VLookup(Target, Sheets("Contracts").Range("A:B"), 2, 0)
    If image <> "" Then
        Sheets("Contracts").Shapes(image).Copy
        Range("F12").PasteSpecial
        Range("C13").Activate
    End If
End Sub

@ Roger

With all the respect to your title, have you seen my suggested video?

Hi

Sorry about that. I hadn't noticed.
try this version which seems to work fine.

I have modified the code to test that the Shape being deleted is a Picture

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim image As String
    If Target.Count > 1 Then Exit Sub
    If Target.Address <> "$C$13" Then Exit Sub
    If Left(Me.Shapes(1).Name, 4) = "Pict" Then
        Me.Shapes(1).Delete
    End If
    image = WorksheetFunction.VLookup(Target, Sheets("Contracts").Range("A:B"), 2, 0)
    If image <> "" Then
        Sheets("Contracts").Shapes(image).Copy
        Range("F12").PasteSpecial
        Range("C13").Activate
    End If
End Sub