Should be simple: Lookup a picture from a folder based on a cell value changing

[Finny388]

I saw
http://www.mcgimpsey.com/excel/lookuppics.html
but where did he keep his pictures?

I just want to show a picture bases on a cell value that is equal to
the name of the jpg in a folder.

When the cell (A1) changes its value, a lookup show the jpg with the
same text of the value.

Change A1 to "Picture5" and C:\Pictures\Picture5.jpg shows up in C1.

bonus: the picture sized to the size of the C1 cell without losing its
aspect ratio

Thanks!

 

[NickHK]

One way is to put an image control on your WS, sized to the correct
dimensions.
Set its .PictureSizeMode to 3 - fmPictureSizeModeZoom.

Then in that WS_Change event:

If not intersect(target,<YourRange>) Is nothing then
Image1.Picture = LoadPicture(Target.Text)
'...etc

You should add error handling in case the graphic does not exist/can't load.

NickHK

 

[Finny388]

One way is to put an image control on your WS, sized to the correct
dimensions.
Set its .PictureSizeMode to 3 - fmPictureSizeModeZoom.

Then in that WS_Change event:

If not intersect(target,<YourRange>) Is nothing then
Image1.Picture = LoadPicture(Target.Text)
'...etc

You should add error handling in case the graphic does not exist/can't load.

NickHK

Thanks Nick,
But how do I do the lookup to the windows folder containing the jpg's?
What event would be the trigger?
TIA

 

[Finny388]

Thanks Nick,
But how do I do thelookupto the windowsfoldercontaining the jpg's?
What event would be the trigger?
TIA

btw, I am talking about a couple hundred pictures, mcgimpsey mentions
that for a large amount, a db solution is better. yikes.

 

[Finny388]

btw, I am talking about a couple hundred pictures, mcgimpsey mentions
that for a large amount, a db solution is better. yikes.

I'd like to trigger by selected value in a combo box.
My code for pulling the pic is so far this: i just need to
trigger it

Sub ImageLookup()
Dim MyPic As IPictureDisp
On Error Resume Next
Set MyPic = LoadPicture("I:\Customers\Quadro Catalogue & Pictures
\SAP Pictures\" & Range("a6").Value & ".jpg")
PicPath = "I:\Customers\Quadro Catalogue & Pictures\SAP Pictures\"
& Range("a6").Value & ".jpg"
H = MyPic.Height
W = MyPic.Width
R1 = W / H
Cells(2, 4).Select
Set pic = ActiveSheet.Pictures.Insert(PickPath)
pic.Height = Cells(2, 4).Height
pic.Width = Cells(2, 4).Height * R1
End Sub

 

[Finny388]

I'd like to trigger by selected value in a combo box.
My code for pulling the pic is so far this: i just need to
trigger it

Sub ImageLookup()
Dim MyPic As IPictureDisp
On Error Resume Next
Set MyPic = LoadPicture("I:\Customers\Quadro Catalogue & Pictures
\SAP Pictures\" & Range("a6").Value & ".jpg")
PicPath = "I:\Customers\Quadro Catalogue & Pictures\SAP Pictures\"
& Range("a6").Value & ".jpg"
H = MyPic.Height
W = MyPic.Width
R1 = W / H
Cells(2, 4).Select
Set pic = ActiveSheet.Pictures.Insert(PickPath)
pic.Height = Cells(2, 4).Height
pic.Width = Cells(2, 4).Height * R1
End Sub

I tried to put it in the Worksheet_Change event handler and see if my
cell value has changed to trigger the code.
Works like a charm if I change the value manually by editing the cell.
But if I try to have it equal to another cell that holds the value, a
value change won't trigger it.
I also tried (and this is how I want to end up) tying a combobox to
the cell ("D5") and that won't trigger it either

any ideas on how to trigger this? is there a combobox change event?

 

[Finny388]

I'd like to trigger by selected value in a combo box.
My code for pulling the pic is so far this: i just need to
trigger it

Sub ImageLookup()
Dim MyPic As IPictureDisp
On Error Resume Next
Set MyPic = LoadPicture("I:\Customers\Quadro Catalogue & Pictures
\SAP Pictures\" & Range("a6").Value & ".jpg")
PicPath = "I:\Customers\Quadro Catalogue & Pictures\SAP Pictures\"
& Range("a6").Value & ".jpg"
H = MyPic.Height
W = MyPic.Width
R1 = W / H
Cells(2, 4).Select
Set pic = ActiveSheet.Pictures.Insert(PickPath)
pic.Height = Cells(2, 4).Height
pic.Width = Cells(2, 4).Height * R1
End Sub

I put it in Worksheet_Change event handler according to John
Walkenbach's VBA book.
It only triggers when "d5" is changed through in an edit.
Not by pointing to another cell that changes.
and not by pointing combobox to it (which is what i want to do)

is there a combobox change event? any ideas on what can be the trigger?

 

[Finny388]

So I found the combobox event handler and now just one last issue.

My combobox DISAPPEARS when I run the sub!
If I comment out Me.Pictures(1).Delete, it works fine.
If I step throught the code - it works fine!!!

I use that delete line to clear out the existing pic before inserting
another. Why would it delete the combobox too? Is there a better way?

Private Sub ComboBox1_Change()
On Error Resume Next
Dim MyPic As IPictureDisp
Me.Pictures(1).Delete
Set MyPic = LoadPicture("I:\Customers\Quadro Catalogue &
Pictures\SAP Pictures\" & Range("D5").Value & ".jpg")
PicPath = "I:\Customers\Quadro Catalogue & Pictures\SAP
Pictures\" & Range("D5").Value & ".jpg"
H = MyPic.Height
W = MyPic.Width
R1 = W / H
Cells(2, 4).Select
Set pic = ActiveSheet.Pictures.Insert(PicPath)
pic.Height = Cells(2, 4).Height
pic.Width = Cells(2, 4).Height * R1
End Sub

 

[Finny388]

So I found the combobox event handler and now just one last issue.

My combobox DISAPPEARS when I run the sub!
If I comment out Me.Pictures(1).Delete, it works fine.
If I step throught the code - it works fine!!!

I use that delete line to clear out the existing pic before inserting
another. Why would it delete the combobox too? Is there a better way?

Private Sub ComboBox1_Change()
On Error Resume Next
Dim MyPic As IPictureDisp
Me.Pictures(1).Delete
Set MyPic = LoadPicture("I:\Customers\Quadro Catalogue &
Pictures\SAP Pictures\" & Range("D5").Value & ".jpg")
PicPath = "I:\Customers\Quadro Catalogue & Pictures\SAP
Pictures\" & Range("D5").Value & ".jpg"
H = MyPic.Height
W = MyPic.Width
R1 = W / H
Cells(2, 4).Select
Set pic = ActiveSheet.Pictures.Insert(PicPath)
pic.Height = Cells(2, 4).Height
pic.Width = Cells(2, 4).Height * R1
End Sub

any help appreciated