July 22nd, 2008, 04:41 PM
posted to microsoft.public.excel.worksheet.functions
|
|
|
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
|