Photo lookup

bouncey · Dec 13, 2018

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 · Dec 13, 2018

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

Yongle · Dec 14, 2018

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?)

Code:

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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1