A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Vertical lookup of pictures



 
 
Thread Tools Display Modes
  #11  
Old July 22nd, 2008, 04:41 PM posted to microsoft.public.excel.worksheet.functions
DJDKAL[_2_]
external usenet poster
 
Posts: 6
Default Vertical lookup of pictures

Thank you so much for the help. It works flawlessly!!

"Ken Johnson" wrote:

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

  #12  
Old July 22nd, 2008, 04:42 PM posted to microsoft.public.excel.worksheet.functions
DJDKAL[_2_]
external usenet poster
 
Posts: 6
Default Vertical lookup of pictures

I did receive it the first time, it just took to long for me to understand
it. The code from the mcgimpsey website was easier for me to understand and
work with. I do appreciate the effort you have put forth in helping me out
though! Thank you!

"Gord Dibben" wrote:

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



  #13  
Old July 22nd, 2008, 07:47 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default Vertical lookup of pictures

I see Ken has given you some new code to deal with the multiple images.

Thanks for the feedback.


Gord

On Tue, 22 Jul 2008 08:42:23 -0700, DJDKAL
wrote:

I did receive it the first time, it just took to long for me to understand
it. The code from the mcgimpsey website was easier for me to understand and
work with. I do appreciate the effort you have put forth in helping me out
though! Thank you!

"Gord Dibben" wrote:

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




  #14  
Old March 22nd, 2010, 10:24 AM posted to microsoft.public.excel.worksheet.functions
tong kimsong
external usenet poster
 
Posts: 1
Default Why i can not protect sheet

Dear Peo and everyone,

i have download sample from http://www.mcgimpsey.com/excel/lookuppics.html

it's look very good for me, but i got some problum
when i protect this worksheet, the picture do not come out.

can u show me what is the problem when we protect sheet and how to do if i want to protect it? if anyone know how to do it please help.
here is my email:

thanks,

regards
Kimsong.



Peo Sjoblom wrote:

Vertical lookup of pictures
14-Jul-08

Not without code, here is an example

http://www.mcgimpsey.com/excel/lookuppics.html

--


Regards,


Peo Sjoblom

Previous Posts In This Thread:

On Monday, July 14, 2008 4:36 PM
DJDKA wrote:

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.

On Monday, July 14, 2008 5:07 PM
Peo Sjoblom wrote:

Vertical lookup of pictures
Not without code, here is an example

http://www.mcgimpsey.com/excel/lookuppics.html

--


Regards,


Peo Sjoblom

On Monday, July 14, 2008 8:22 PM
Gord Dibben wrote:

Which requires the pictures to be in the same worksheet as the VLOOKUP and
Which requires the pictures to be in the same worksheet as the VLOOKUP and the
code.


Gord Dibben MS Excel MVP

On Tuesday, July 15, 2008 10:25 AM
daniel.kalfaya wrote:

Thank you for the quick response.
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:

On Tuesday, July 15, 2008 3:28 PM
Gord Dibben wrote:

Got to Debra Dalgeish's site for a sample workbook from Bernie Dietrick
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:

On Monday, July 21, 2008 10:29 AM
daniel.kalfaya wrote:

I have the pictures, the code, and the VLOOKUP formula in the same tab.
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:

On Tuesday, July 22, 2008 7:12 AM
Ken Johnson wrote:

Vertical lookup of pictures
On Jul 22, 12:29 am, DJDKAL wrote:

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

On Tuesday, July 22, 2008 9:43 AM
daniel.kalfaya wrote:

That works GREAT!
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 Tuesday, July 22, 2008 11:31 AM
Gord Dibben wrote:

I guess you missed this post but here it is again.
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:

On Tuesday, July 22, 2008 11:41 AM
daniel.kalfaya wrote:

Vertical lookup of pictures
Thank you so much for the help. It works flawlessly!!

"Ken Johnson" wrote:

On Tuesday, July 22, 2008 11:42 AM
daniel.kalfaya wrote:

I did receive it the first time, it just took to long for me to understand it.
I did receive it the first time, it just took to long for me to understand
it. The code from the mcgimpsey website was easier for me to understand and
work with. I do appreciate the effort you have put forth in helping me out
though! Thank you!

"Gord Dibben" wrote:

On Tuesday, July 22, 2008 2:47 PM
Gord Dibben wrote:

I see Ken has given you some new code to deal with the multiple images.
I see Ken has given you some new code to deal with the multiple images.

Thanks for the feedback.


Gord

wrote:

On Saturday, July 26, 2008 12:19 AM
Ken Johnson wrote:

Vertical lookup of pictures
On Jul 22, 11:43 pm, DJDKAL wrote:

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


Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF TreeView Control With Filtering of Nodes Based on Their Levels
http://www.eggheadcafe.com/tutorials...trol-with.aspx
  #15  
Old March 22nd, 2010, 04:24 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default Why i can not protect sheet

You have to unlock the DV dropdown cell and the VLOOKUP cell which in John's
example book is F1

Then protect the sheet.


Gord Dibben MS Excel MVP

On Mon, 22 Mar 2010 02:24:25 -0700, tong kimsong wrote:

Dear Peo and everyone,

i have download sample from http://www.mcgimpsey.com/excel/lookuppics.html

it's look very good for me, but i got some problum
when i protect this worksheet, the picture do not come out.

can u show me what is the problem when we protect sheet and how to do if i want to protect it? if anyone know how to do it please help.
here is my email:

thanks,

regards
Kimsong.



Peo Sjoblom wrote:

Vertical lookup of pictures
14-Jul-08

Not without code, here is an example

http://www.mcgimpsey.com/excel/lookuppics.html

--


Regards,


Peo Sjoblom

Previous Posts In This Thread:

On Monday, July 14, 2008 4:36 PM
DJDKA wrote:

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.

On Monday, July 14, 2008 5:07 PM
Peo Sjoblom wrote:

Vertical lookup of pictures
Not without code, here is an example

http://www.mcgimpsey.com/excel/lookuppics.html


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:58 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.