Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Should be simple: Lookup a picture from a folder based on a cell value changing
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Should be simple: Lookup a picture from a folder based on a cell value changing
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" wrote in message oups.com... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Should be simple: Lookup a picture from a folder based on a cell value changing
On Apr 18, 11:53 pm, "NickHK" wrote:
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" wrote in message oups.com... 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! Thanks Nick, But how do I do the lookup to the windows folder containing the jpg's? What event would be the trigger? TIA |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Should be simple: Lookup a picture from a folder based on a cell value changing
On Apr 19, 7:56 am, Finny388 wrote:
On Apr 18, 11:53 pm, "NickHK" wrote: 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" wrote in message roups.com... I saw http://www.mcgimpsey.com/excel/lookuppics.html but where did he keep his pictures? I just want to show apicturebases on a cell value that is equal to the name of the jpg in afolder. When the cell (A1) changes its value, alookupshow the jpg with the same text of the value. Change A1 to "Picture5" and C:\Pictures\Picture5.jpg shows up in C1. bonus: thepicturesized to the size of the C1 cell without losing its aspect ratio Thanks! 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Should be simple: Lookup a picture from a folder based on a cell value changing
On Apr 19, 8:29 am, Finny388 wrote:
On Apr 19, 7:56 am, Finny388 wrote: On Apr 18, 11:53 pm, "NickHK" wrote: 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" wrote in message roups.com... I saw http://www.mcgimpsey.com/excel/lookuppics.html but where did he keep his pictures? I just want to show apicturebases on a cell value that is equal to the name of the jpg in afolder. When the cell (A1) changes its value, alookupshow the jpg with the same text of the value. Change A1 to "Picture5" and C:\Pictures\Picture5.jpg shows up in C1. bonus: thepicturesized to the size of the C1 cell without losing its aspect ratio Thanks! 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. 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Should be simple: Lookup a picture from a folder based on a cell value changing
On Apr 19, 9:31 am, Finny388 wrote:
On Apr 19, 8:29 am, Finny388 wrote: On Apr 19, 7:56 am, Finny388 wrote: On Apr 18, 11:53 pm, "NickHK" wrote: 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" wrote in message roups.com... I saw http://www.mcgimpsey.com/excel/lookuppics.html but where did he keep his pictures? I just want to show apicturebases on a cell value that is equal to the name of the jpg in afolder. When the cell (A1) changes its value, alookupshow the jpg with the same text of the value. Change A1 to "Picture5" and C:\Pictures\Picture5.jpg shows up in C1. bonus: thepicturesized to the size of the C1 cell without losing its aspect ratio Thanks! 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. 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Should be simple: Lookup a picture from a folder based on a cell value changing
On Apr 19, 9:31 am, Finny388 wrote:
On Apr 19, 8:29 am, Finny388 wrote: On Apr 19, 7:56 am, Finny388 wrote: On Apr 18, 11:53 pm, "NickHK" wrote: 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" wrote in message roups.com... I saw http://www.mcgimpsey.com/excel/lookuppics.html but where did he keep his pictures? I just want to show apicturebases on a cell value that is equal to the name of the jpg in afolder. When the cell (A1) changes its value, alookupshow the jpg with the same text of the value. Change A1 to "Picture5" and C:\Pictures\Picture5.jpg shows up in C1. bonus: thepicturesized to the size of the C1 cell without losing its aspect ratio Thanks! 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. 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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Should be simple: Lookup a picture from a folder based on a cell value changing
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Should be simple: Lookup a picture from a folder based on a cell value changing
On Apr 19, 11:20 am, Finny388 wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup cell for documents in a folder | Excel Worksheet Functions | |||
Changing a Picture or Object if a Cell equals a certain value | Excel Discussion (Misc queries) | |||
save to folder based on cell value | Excel Discussion (Misc queries) | |||
How to add picture based on a cell value | Excel Discussion (Misc queries) | |||
Looping folder changing - Save Preview Picture | Excel Programming |