If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Vertical lookup of pictures
I am trying to see if there is a way to lookup a picture in one tab and
insert it into another tab all within the same worksheet. I have tried the embed function, as well as creating a hyperlink to the picture only to find the VLOOKUP just returns the text of the link. Please help. |
#2
|
|||
|
|||
Vertical lookup of pictures
Not without code, here's an example
http://www.mcgimpsey.com/excel/lookuppics.html -- Regards, Peo Sjoblom "DJDKAL" wrote in message ... I am trying to see if there is a way to lookup a picture in one tab and insert it into another tab all within the same worksheet. I have tried the embed function, as well as creating a hyperlink to the picture only to find the VLOOKUP just returns the text of the link. Please help. |
#3
|
|||
|
|||
Vertical lookup of pictures
Which requires the pictures to be in the same worksheet as the VLOOKUP and the
code. Gord Dibben MS Excel MVP On Mon, 14 Jul 2008 14:07:38 -0700, "Peo Sjoblom" wrote: Not without code, here's an example http://www.mcgimpsey.com/excel/lookuppics.html -- Regards, Peo Sjoblom "DJDKAL" wrote in message ... I am trying to see if there is a way to lookup a picture in one tab and insert it into another tab all within the same worksheet. I have tried the embed function, as well as creating a hyperlink to the picture only to find the VLOOKUP just returns the text of the link. Please help. |
#4
|
|||
|
|||
Vertical lookup of pictures
Thank you for the quick response. That works great if I have a couple of
pictures in the entire workbook. In this instance, I have hundreds (which sounds like it might be a database feature, according to the website you posted which is another demon for me to learn). In addition, there will be multiple cases where I need to show two or more pictures with one lookup. For example, I type a parcel number of a property into my input tab. The other lookup formulas return the contact information of the owner, name, telephone number, address, etc. of that property. I would like to show multiple pictures of that parcel when I type the number. "Peo Sjoblom" wrote: Not without code, here's an example http://www.mcgimpsey.com/excel/lookuppics.html -- Regards, Peo Sjoblom "DJDKAL" wrote in message ... I am trying to see if there is a way to lookup a picture in one tab and insert it into another tab all within the same worksheet. I have tried the embed function, as well as creating a hyperlink to the picture only to find the VLOOKUP just returns the text of the link. Please help. |
#5
|
|||
|
|||
Vertical lookup of pictures
Got to Debra Dalgeish's site for a sample workbook from Bernie Dietrick for
adding pictures to selections. Alows for more than one picture to be displayed based upon a value or item chosen from DV lists http://www.contextures.on.ca/excelfiles.html#DataVal DV0049 - ClipArt Selection Edit the code to suit. Gord Dibben MS Excel MVP On Tue, 15 Jul 2008 07:25:00 -0700, DJDKAL wrote: Thank you for the quick response. That works great if I have a couple of pictures in the entire workbook. In this instance, I have hundreds (which sounds like it might be a database feature, according to the website you posted which is another demon for me to learn). In addition, there will be multiple cases where I need to show two or more pictures with one lookup. For example, I type a parcel number of a property into my input tab. The other lookup formulas return the contact information of the owner, name, telephone number, address, etc. of that property. I would like to show multiple pictures of that parcel when I type the number. "Peo Sjoblom" wrote: Not without code, here's an example http://www.mcgimpsey.com/excel/lookuppics.html -- Regards, Peo Sjoblom "DJDKAL" wrote in message ... I am trying to see if there is a way to lookup a picture in one tab and insert it into another tab all within the same worksheet. I have tried the embed function, as well as creating a hyperlink to the picture only to find the VLOOKUP just returns the text of the link. Please help. |
#6
|
|||
|
|||
Vertical lookup of pictures
I have the pictures, the code, and the VLOOKUP formula in the same tab. I
have the lookup table in another tab. I am getting "Run-time error '1004': Unable to set the Visible property of the Picture class" when I try to run the code. The code is identical to the code copied from the below web address, with the exception of the cell where I want the pictures to show. Please help. "Gord Dibben" wrote: Which requires the pictures to be in the same worksheet as the VLOOKUP and the code. Gord Dibben MS Excel MVP On Mon, 14 Jul 2008 14:07:38 -0700, "Peo Sjoblom" wrote: Not without code, here's an example http://www.mcgimpsey.com/excel/lookuppics.html -- Regards, Peo Sjoblom "DJDKAL" wrote in message ... I am trying to see if there is a way to lookup a picture in one tab and insert it into another tab all within the same worksheet. I have tried the embed function, as well as creating a hyperlink to the picture only to find the VLOOKUP just returns the text of the link. Please help. |
#7
|
|||
|
|||
Vertical lookup of pictures
On Jul 22, 12:29 am, DJDKAL wrote:
I have the pictures, the code, and the VLOOKUP formula in the same tab. I have the lookup table in another tab. I am getting "Run-time error '1004': Unable to set the Visible property of the Picture class" when I try to run the code. The code is identical to the code copied from the below web address, with the exception of the cell where I want the pictures to show. Please help. "Gord Dibben" wrote: Which requires the pictures to be in the same worksheet as the VLOOKUP and the code. Gord Dibben MS Excel MVP On Mon, 14 Jul 2008 14:07:38 -0700, "Peo Sjoblom" wrote: Not without code, here's an example http://www.mcgimpsey.com/excel/lookuppics.html -- Regards, Peo Sjoblom "DJDKAL" wrote in message ... I am trying to see if there is a way to lookup a picture in one tab and insert it into another tab all within the same worksheet. I have tried the embed function, as well as creating a hyperlink to the picture only to find the VLOOKUP just returns the text of the link. Please help. Are you using McGimpsey's LookUpPics and do you have a large number of pictures on the sheet (maybe more than 50)? If so, I have seen this problem before. After so many pictures the oPic Picture object creates the problem. A workaround I have used in the past is to avoid the Picture object (Dim oPic as Picture) and instead use the Shape object (Dim shpPic as Shape). Then use the Shape's Type property (for Pictures, Type=13) to process Pictures and leave all the other shapes alone. This sample code positions the visible picture in A1 when its name is returned by a formula (VLookup) in A1... Private Sub Worksheet_Calculate() Dim shpPic As Shape Application.ScreenUpdating = False For Each shpPic In Me.Shapes Select Case shpPic.Type Case 13 Select Case shpPic.Name Case Range("A1").Text shpPic.Visible = True With Range("A1") shpPic.Top = .Top shpPic.Left = .Left End With Case Else shpPic.Visible = False End Select End Select Next shpPic End Sub Ken Johnson |
#8
|
|||
|
|||
Vertical lookup of pictures
That works GREAT! Next and hopefully last question.... I want to show
pictures at the same time, in cells C23, I23, C41, and I41. I changed the "A1" from your code to C23 and it worked. What part of the code do I need to copy and where do I insert it so that the other three cells also show pictures? "Ken Johnson" wrote: On Jul 22, 12:29 am, DJDKAL wrote: I have the pictures, the code, and the VLOOKUP formula in the same tab. I have the lookup table in another tab. I am getting "Run-time error '1004': Unable to set the Visible property of the Picture class" when I try to run the code. The code is identical to the code copied from the below web address, with the exception of the cell where I want the pictures to show. Please help. "Gord Dibben" wrote: Which requires the pictures to be in the same worksheet as the VLOOKUP and the code. Gord Dibben MS Excel MVP On Mon, 14 Jul 2008 14:07:38 -0700, "Peo Sjoblom" wrote: Not without code, here's an example http://www.mcgimpsey.com/excel/lookuppics.html -- Regards, Peo Sjoblom "DJDKAL" wrote in message ... I am trying to see if there is a way to lookup a picture in one tab and insert it into another tab all within the same worksheet. I have tried the embed function, as well as creating a hyperlink to the picture only to find the VLOOKUP just returns the text of the link. Please help. Are you using McGimpsey's LookUpPics and do you have a large number of pictures on the sheet (maybe more than 50)? If so, I have seen this problem before. After so many pictures the oPic Picture object creates the problem. A workaround I have used in the past is to avoid the Picture object (Dim oPic as Picture) and instead use the Shape object (Dim shpPic as Shape). Then use the Shape's Type property (for Pictures, Type=13) to process Pictures and leave all the other shapes alone. This sample code positions the visible picture in A1 when its name is returned by a formula (VLookup) in A1... Private Sub Worksheet_Calculate() Dim shpPic As Shape Application.ScreenUpdating = False For Each shpPic In Me.Shapes Select Case shpPic.Type Case 13 Select Case shpPic.Name Case Range("A1").Text shpPic.Visible = True With Range("A1") shpPic.Top = .Top shpPic.Left = .Left End With Case Else shpPic.Visible = False End Select End Select Next shpPic End Sub Ken Johnson |
#9
|
|||
|
|||
Vertical lookup of pictures
On Jul 22, 11:43 pm, DJDKAL wrote:
That works GREAT! Next and hopefully last question.... I want to show pictures at the same time, in cells C23, I23, C41, and I41. I changed the "A1" from your code to C23 and it worked. What part of the code do I need to copy and where do I insert it so that the other three cells also show pictures? "Ken Johnson" wrote: On Jul 22, 12:29 am, DJDKAL wrote: I have the pictures, the code, and the VLOOKUP formula in the same tab. I have the lookup table in another tab. I am getting "Run-time error '1004': Unable to set the Visible property of the Picture class" when I try to run the code. The code is identical to the code copied from the below web address, with the exception of the cell where I want the pictures to show. Please help. "Gord Dibben" wrote: Which requires the pictures to be in the same worksheet as the VLOOKUP and the code. Gord Dibben MS Excel MVP On Mon, 14 Jul 2008 14:07:38 -0700, "Peo Sjoblom" wrote: Not without code, here's an example http://www.mcgimpsey.com/excel/lookuppics.html -- Regards, Peo Sjoblom "DJDKAL" wrote in message ... I am trying to see if there is a way to lookup a picture in one tab and insert it into another tab all within the same worksheet. I have tried the embed function, as well as creating a hyperlink to the picture only to find the VLOOKUP just returns the text of the link. Please help. Are you using McGimpsey's LookUpPics and do you have a large number of pictures on the sheet (maybe more than 50)? If so, I have seen this problem before. After so many pictures the oPic Picture object creates the problem. A workaround I have used in the past is to avoid the Picture object (Dim oPic as Picture) and instead use the Shape object (Dim shpPic as Shape). Then use the Shape's Type property (for Pictures, Type=13) to process Pictures and leave all the other shapes alone. This sample code positions the visible picture in A1 when its name is returned by a formula (VLookup) in A1... Private Sub Worksheet_Calculate() Dim shpPic As Shape Application.ScreenUpdating = False For Each shpPic In Me.Shapes Select Case shpPic.Type Case 13 Select Case shpPic.Name Case Range("A1").Text shpPic.Visible = True With Range("A1") shpPic.Top = .Top shpPic.Left = .Left End With Case Else shpPic.Visible = False End Select End Select Next shpPic End Sub Ken Johnson Hi, To do that requires two separate loops. The first loop hides all the pictures. The second loop shows the relevant picture for each of the four cells... Private Sub Worksheet_Calculate() Dim shpPic As Shape Application.ScreenUpdating = False For Each shpPic In Me.Shapes If shpPic.Type = 13 Then shpPic.Visible = False End If Next shpPic For Each shpPic In Me.Shapes Select Case shpPic.Name Case Range("C23").Text shpPic.Visible = True With Range("C23") shpPic.Top = .Top shpPic.Left = .Left End With Case Range("I23").Text shpPic.Visible = True With Range("I23") shpPic.Top = .Top shpPic.Left = .Left End With Case Range("C41").Text shpPic.Visible = True With Range("C41") shpPic.Top = .Top shpPic.Left = .Left End With Case Range("I41").Text shpPic.Visible = True With Range("I41") shpPic.Top = .Top shpPic.Left = .Left End With End Select Next shpPic End Sub Ken Johnson |
#10
|
|||
|
|||
Vertical lookup of pictures
I guess you missed this post but here it is again.
Go to Debra Dalgeish's site for a sample workbook from Bernie Dietrick for adding pictures to selections. Alows for more than one picture to be displayed based upon a value or item chosen from DV lists http://www.contextures.on.ca/excelfiles.html#DataVal DV0049 - ClipArt Selection Edit the code to suit. Gord On Tue, 22 Jul 2008 06:43:01 -0700, DJDKAL wrote: That works GREAT! Next and hopefully last question.... I want to show pictures at the same time, in cells C23, I23, C41, and I41. I changed the "A1" from your code to C23 and it worked. What part of the code do I need to copy and where do I insert it so that the other three cells also show pictures? "Ken Johnson" wrote: On Jul 22, 12:29 am, DJDKAL wrote: I have the pictures, the code, and the VLOOKUP formula in the same tab. I have the lookup table in another tab. I am getting "Run-time error '1004': Unable to set the Visible property of the Picture class" when I try to run the code. The code is identical to the code copied from the below web address, with the exception of the cell where I want the pictures to show. Please help. "Gord Dibben" wrote: Which requires the pictures to be in the same worksheet as the VLOOKUP and the code. Gord Dibben MS Excel MVP On Mon, 14 Jul 2008 14:07:38 -0700, "Peo Sjoblom" wrote: Not without code, here's an example http://www.mcgimpsey.com/excel/lookuppics.html -- Regards, Peo Sjoblom "DJDKAL" wrote in message ... I am trying to see if there is a way to lookup a picture in one tab and insert it into another tab all within the same worksheet. I have tried the embed function, as well as creating a hyperlink to the picture only to find the VLOOKUP just returns the text of the link. Please help. Are you using McGimpsey's LookUpPics and do you have a large number of pictures on the sheet (maybe more than 50)? If so, I have seen this problem before. After so many pictures the oPic Picture object creates the problem. A workaround I have used in the past is to avoid the Picture object (Dim oPic as Picture) and instead use the Shape object (Dim shpPic as Shape). Then use the Shape's Type property (for Pictures, Type=13) to process Pictures and leave all the other shapes alone. This sample code positions the visible picture in A1 when its name is returned by a formula (VLookup) in A1... Private Sub Worksheet_Calculate() Dim shpPic As Shape Application.ScreenUpdating = False For Each shpPic In Me.Shapes Select Case shpPic.Type Case 13 Select Case shpPic.Name Case Range("A1").Text shpPic.Visible = True With Range("A1") shpPic.Top = .Top shpPic.Left = .Left End With Case Else shpPic.Visible = False End Select End Select Next shpPic End Sub Ken Johnson |
|
Thread Tools | |
Display Modes | |
|
|