Photo lookup

[bouncey]

Hi there,

It has been a very long time since I have been on here

I have a problem which I do not know where to start. I have a cell (a5) where I can change the part number, and I am trying to lookup to a photo that will be in any one of multiple folders and return the image to cell a20.

Any and all help appreciated.

 

[ncrcnbl]

how many part numbers, and how many different folders? Are you already using the DIR function in VBA?

 

[Yongle]

Simplest way,
- create a 2 column lookup in another sheet ( here named "Images")
- insert an active-x image control in cell A20 ( here named "Image1")
- add Change Event macro to sheet module (code is a one liner )
- change in value in A5 triggers change event macro
- VBA looks up value of A5 in sheet "Images" and returns full path to file
- image loaded into image control
- error handling should be added in case VLookup finds no match or image path is incorrect (load a default image?)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "A5" Then
        Me.[COLOR=#006400]Image1[/COLOR].Picture = LoadPicture(WorksheetFunction.VLookup(Target, Sheets([COLOR=#ff0000]"Images[/COLOR]").Range("A:B"), 2, 0))
    End If
End Sub

Excel 2016 (Windows) 32 bit

	A​
1​
2​
3​
4​
5​	123​
6​
19​
20​
21​
22​

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	A​	B​
1​	Part Number​	File Path and Name​
2​	456​	C:\Parts\ABC\Part456.jpg​
3​	123​	C:\Parts\XYZ\Part123.jpg​
4​	789​	C:\Parts\XYZ\Part789.jpg​
5​	etc​	​

Sheet: Images