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]

Please try the following on a copy of your workbook. It's a worksheet_change event module so you need to put it in the sheet module of the sheet where you want the pictures to go. Right click the sheet tab, select View Code and put the code in the window that appears on the right of screen.
I've called the sheet with the pictures "data" - change this in the code to the actual sheet name.
I've assumed the dropdown cells are B3 and B11 - again change these to suit. Also assumed the top left cells to put the pictures are D2 and D10 respectively, change to suit.
The code works if and only if you give the pictures on the second sheet the exact same names as the dropdown choices.
The code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("B3,B11"), Target) Is Nothing Then
        Application.EnableEvents = False
        Dim img As Object, pick As String, x As String
        pick = Target.Value
        Set img = Worksheets("data").Shapes(pick) '<-- *** change to sheet name where pictures are ***
        x = Target.Value
        
        On Error Resume Next
        Me.Shapes(x).Delete
        On Error GoTo 0
        
        If Target.Address = ["$B$3"] Then
            img.Copy
            Me.Paste [D2]
            [B3].Select
        ElseIf Target.Address = ["$B$11"] Then
            img.Copy
            Me.Paste [D10]
            [B11].Select
        End If
    End If
    Application.EnableEvents = True
End Sub

Link to demo file: picture copy.xlsm

 

[leaper1981]

Hi Kevin,

Thanks for your reply, I'm sorry for being very poor at giving the correct information. I really tried to make the changes to the cells I've actually used (which I can do) but I think I really messed you about by not showing exactly how I had set it up (which might not be the best way). I think it won't work for me as my drop down list is on a different tab.
So I've now included pictures that show the cells and tabs! which probably makes it a lot easier.

I have the main document I want the customer to use (SAMPLE REQUEST) tab, then the drop down list for the styles is on the (DATA) tab, and the pictures are on the (CARTONS&FITMENTS) tab.

The only difference from your (picture copy) I would like is that the drop down info isn't on the Sheet1 tab (in your example), rather on the data tab (so its sort of hidden) One other thing, I'm not sure why but although your example looks perfect the drop downs don't work when I try them, not sure why.

Lastly, why can't actual excel documents be shared on here? I only ask as I'm sure you'd do this in 2 secs if I could of sent it to you.

Anyway, again sorry for not giving all the info from the off, if you can help again at all that would be amazing.

Thanks again,
Dan

 

[kevin9999]

It was really just a matter of changing the cell & sheet references for it to work. The code below is based on your new images. Here's the link to the updated file: picture copy.xlsm
You can't upload a file to the forum, but you can post useful copies of your sheets using the XL2BB add in, and you can also share your file(s) via Google Drive (as I have done) Dropbox, os=r similar file sharing platform.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("B43,B51"), Target) Is Nothing Then
        Application.EnableEvents = False
        Dim img As Object, pick As String, x As String
        pick = Target.Value
        Set img = Worksheets("Carton&fitments").Shapes(pick)
        x = Target.Value
        
        On Error Resume Next
        Me.Shapes(x).Delete
        On Error GoTo 0
        
        If Target.Address = ["$B$43"] Then
            img.Copy
            Me.Paste [D42]
            [B43].Select
        ElseIf Target.Address = ["$B$51"] Then
            img.Copy
            Me.Paste [D50]
            [B51].Select
        End If
    End If
    Application.EnableEvents = True
End Sub

 

[leaper1981]

Hi Kevin,

Happy new year, hope you had a good xmas!

Sorry I'm back with silly questions, I tried the last method you gave me but literally nothing happened, even when I changed the cell references to the correct ones it didnt even come back with an error. I know this will all be to do with the way I've done it so I've tried to use this (mini-sheet) to see if that allows you to see what I've done. Hoping this helps, I had to get rid of data when making a mini sheet as it said there were merged cells & it wouldn't do it.

PCI SR.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2				CAD SAMPLE REQUEST FORM
3
4
5
6
7			DATE:
8
9			Project Managers Name:
10
11			Project ID Reference:
12
13			To provide a CAD sample we will require the following information
14
15			Please provide the following dimensions of the vial
16
17
18
19
20
21			Height of Vial		Diameter of Vial				Shoulder Height
22			(Including cap)		(Including Label)				(Base to shoulder)
23
24
25			Number of vials required?				Temperature vials will be stored at?
26
27			If you wish to pack an item that is not a vial please provide the dimensions and picture
28
29						Paste picture here
30			Width of product:
31
32
33			Height of product:
34
35
36			Depth of product:
37
38
39			Quantity required:
40
41
42
43			Carton style required
44			CLB
45
46
47
48
49
50
51			Preferred fitment style
52
53
54
55
56
57
58
SAMPLE REQUEST

 

[leaper1981]

wasnt sure if I could post 3 mini sheets in one message so I've sent them separately

PCI SR.xlsm
	A	B	C	D
1
2	N/A
3	1
4	2
5	3
6	4
7	5
8	6
9	7
10	8
11	9
12	10
13	11
14	12
15	13
16	14
17	15
18	16
19	17
20	18
21	19
22	20
23	21
24	22
25	23
26	24
27	25
28	26
29	27
30	28
31	29
32	30
33
34	CLB
35	CLB with fitment
36	Peglock Tray
37	Peglock with fitment
38	RTE
39	RTE with fitment
40	OTE
41	OTE with fitment
42	Envelope base
43	Envelope with fitment
44
45	Style 1
46	Style 2
47	Style 3
48	Style 4
49	Style 5
DATA

 

[leaper1981]

PCI SR.xlsm
	A	B	C
1	Carton Styles	Carton photos
2	CLB
3	CLB with fitment
4	Peglock Tray
5	Peglock with fitment
6	RTE
7	RTE with fitment
8	OTE
9	OTE with fitment
10	Envelope base
11	Envelope with fitment
Carton&fitments

 

[kevin9999]

The simplest way forward with this would be for you to share your actual file via Google Drive, Dropbox or similar file sharing platform. Otherwise we could be going back and forth indefinitely.

 

[leaper1981]

Not sure if I've done this right but I'm trying...........as I'm sure you're very aware of, trying in more than one way lol

https://drive.google.com/file/d/10MP58gTolnufy9sQa3F5IfjOjB8eRQG3/view?usp=drive_link

 

[leaper1981]

Nope just realised that link requires you to sign in to my google account, sorry for being stupid I've got it in the google drive I'm not sure how to give it to you