Picture appear when certain drop down list info selected.

[leaper1981]

Hi,

Just wondering if anyone can help at all. I have a sheet with a drop down list & another sheet with pictures that relate to each of the options in the drop down list on the main sheet. I want the pictures to appear in a box next to the drop down list when a certain one is selected. I did see a tutorial but it seemed to have the pictures on the same sheet whereas I want to keep the pictures on a different sheet. Not sure if I've done a good enough job of explaining here but I'm sure its very simple for someone that understands this better. Any help would be great. I've included pictures, the drop down list is on the main document shhet & the pictures are on a separate 'data' sheet.......im calling it.

Thanks in advance.

Dan

 

[kevin9999]

You need to make the file available to "Anyone with the link" then copy the link to here. See this.

 

[leaper1981]

aha! I think I've done it now - PCI SR.xlsm

 

[kevin9999]

Got it now - leave it with me, I'll look at this tomorrow.

 

[leaper1981]

Thanks Kevin, sorry for being so useless, I really appreciate your help.

 

[kevin9999]

It’s difficult to say why the code I provided in post #4 didn’t work for you on the file you shared, because the file you shared didn’t contain any sheet module code at all. I also said in post #2: “The code works if and only if you give the pictures on the second sheet the exact same names as the dropdown choices.” I note that you hadn’t done that – the images on the Carton&fitments sheet were still called “Graphic 1”, “Picture 9” etc. Looks like you named the cells they were in as named ranges instead.

It would take more time than I’m willing to spend to get your file to the place where you seem to want it to be, however, I am willing to push you in the right direction. The code below has been adjusted to match your cell references on the SAMPLE REQUEST sheet. I have also changed the names of your images on the Carton&fitments sheet to match your dropdown choices. You will see that when you change the dropdown in cell C44 that the selected image will be returned to cell F43. Link to the amended file: PCI SR.xlsm

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("C44,C52"), Target) Is Nothing Then
        Application.EnableEvents = False
        Dim img As Object, pick As String, x As String
        pick = Target.Value
        If Not shapeExists(pick) Then
            MsgBox "Image not found!"
            Application.EnableEvents = True
            Exit Sub
        End If
        Set img = Worksheets("Carton&fitments").Shapes(pick)
        If Not img Is Nothing Then
        x = Target.Value
        
        On Error Resume Next
        Me.Shapes(x).Delete
        On Error GoTo 0
        
        If Target.Address = ["$C$44"] Then
            img.Copy
            Me.Paste [F43]
            [C44].Select
        ElseIf Target.Address = ["$C$52"] Then
            img.Copy
            Me.Paste [F51]
            [C52].Select
        End If
        End If
    End If
    Application.EnableEvents = True
End Sub
Function shapeExists(pick As String) As Boolean
    Dim sh As Shape
    For Each sh In Worksheets("Carton&fitments").Shapes
         If sh.Name = pick Then shapeExists = True
    Next sh
End Function

 

[leaper1981]

Hi Kevin,

That's brilliant thanks. I sent it without any of the code as I didn't want my messing to confuse the situation, This is doing exactly as I wanted, I know there's pictures missing etc, I wasn't expecting you to do it all for me, I just needed that push as you say lol. I'm very sorry for being a bit useless with this I really appreciate all your time, effort and help.

Thank you again.

Dan

 

[kevin9999]

You're welcome Dan, and thanks for the feedback