Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Image changed based on vlookup function
I'm wondering if this is possible...
I'm setting up a worksheet that uses a vlookup function to change a particular value. That all works like a charm. The values all represent a particular product, I would like the product image to change when the vlookup is ran. I have all the images on a separate sheet. Any help would be greatly appreciated. Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Image changed based on vlookup function
The VBA solution provided by JE McGimpsey is the generally accepted best
approach: http://www.mcgimpsey.com/excel/lookuppics.html Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Dave" wrote: I'm wondering if this is possible... I'm setting up a worksheet that uses a vlookup function to change a particular value. That all works like a charm. The values all represent a particular product, I would like the product image to change when the vlookup is ran. I have all the images on a separate sheet. Any help would be greatly appreciated. Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Image changed based on vlookup function
Yes, thank you.
The spreadsheet contains many images so I'm not sure if this is the solution to go with. I'll definitely give it a shot. Thanks again! "Ron Coderre" wrote: The VBA solution provided by JE McGimpsey is the generally accepted best approach: http://www.mcgimpsey.com/excel/lookuppics.html Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Dave" wrote: I'm wondering if this is possible... I'm setting up a worksheet that uses a vlookup function to change a particular value. That all works like a charm. The values all represent a particular product, I would like the product image to change when the vlookup is ran. I have all the images on a separate sheet. Any help would be greatly appreciated. Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Image changed based on vlookup function
Ok that could be done. First of all select cell A1 and go to the top menu that says *Data* then *Validation* then you would see 3 tabs go to *Settings* then in the allow box look for *List* then select your range on your worksheet that has the names of your pictures and hit OK Second: Go to the top menu and right click on *Insert* then look for the word *Control Box* select it. Then you'll see option buttons look for the *Image Control* button and select it. Then select D1 the image control box will appear. Then make sure to hit the Exit Design mode. If you dont know whats a Exit Design mode take a look at the link below. http://www.contextures.com/xlDataVal11.html Third now right click on your worksheet tab and select *View Code* then paste this code you see below Code: -------------------- Private Sub Worksheet_Change(ByVal Target As Range) Dim strPath As String If Intersect(Target, Range("$A$1")) Is Nothing Then Exit Sub If Target.Cells.Count 1 Then Exit Sub ffname = PicPath & tbxpartnr & ".jpg" strPath = "C:\MyPicture\" 'Choose your file path On Error Resume Next Image1.Picture = LoadPicture(strPath & Target.Text & ".jpg") If Err = "53" Then MsgBox "Picture Does Not Exist : " End If End Sub -------------------- -- vane0326 ------------------------------------------------------------------------ vane0326's Profile: http://www.excelforum.com/member.php...o&userid=14731 View this thread: http://www.excelforum.com/showthread...hreadid=521397 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Image changed based on vlookup function
Perhaps this non-vba alternative would be suitable:
Assumption: Pictures are stored on Sheet2 to be dynamically shown on Sheet1. Select Sheet2 and turn off Grid Lines (ToolsOptionsView tab:Uncheck Grid Lines) 1)For each picture to be displayed: 1a. InsertPicture from file. (select picture and put it in the sheet). 1b. Select the range of cells that contains the picture. 1c. Name that range of cells, using the prefix "pic" followed by the dropdown list text: Example for a picture of an Elephant: InsertNameDefine Name: picElephant 2)Build your data validation list on a cell in Sheet1 and pick one of the items. 3)Create a dynamic range name that refers to that cell: InsertNameDefine Name: ShowMyPic RefersTo: =INDIRECT("pic"&Sheet1!$A$1) ....or whatever cell you chose. 4)Copy/Paste one of the pictures from Sheet2 to the display cell on Sheet1. 5)With picture selected, type this in the formula bar, then press [Enter]: =ShowMyPic The picture will be replaced by the picture referred to by the dropdown list. Each time you select a different item in the list, the associated picture will appear in the picture box and resize appropriately. Something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "Dave" wrote: Yes, thank you. The spreadsheet contains many images so I'm not sure if this is the solution to go with. I'll definitely give it a shot. Thanks again! "Ron Coderre" wrote: The VBA solution provided by JE McGimpsey is the generally accepted best approach: http://www.mcgimpsey.com/excel/lookuppics.html Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Dave" wrote: I'm wondering if this is possible... I'm setting up a worksheet that uses a vlookup function to change a particular value. That all works like a charm. The values all represent a particular product, I would like the product image to change when the vlookup is ran. I have all the images on a separate sheet. Any help would be greatly appreciated. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DISPLAY Image Based on Logic | Excel Discussion (Misc queries) | |||
changing image based on pulldown | Excel Programming | |||
format cell based on results of vlookup function | Excel Worksheet Functions | |||
How do I have an image in a cell based upon a referenced value? | Excel Programming | |||
Embedded word doc changed to image-need to change back to word. | Excel Discussion (Misc queries) |