PDA

View Full Version : Enter image based on cell value



sudip009
04-09-2009, 03:32 PM
Hi,
I need a vb script to display a image in column A based on the cell value of column B.
For example, if the value of column B is Carrera, column A should display carrera.bmp.

The images reside on c:\images

Thanks in advance.

Krishna Kumar
04-09-2009, 07:14 PM
Hi,

Try this UDF.

Function ShowPicD(PicFile As String) As Boolean
'By Damon Ostrander
'http://www.mrexcel.com/forum/showthread.php?t=100737&
Dim AC As Range
Static P As Shape
On Error GoTo Done
Set AC = Application.Caller
If PicExists(P) Then
P.Delete
Else
'look for a picture already over cell
For Each P In ActiveSheet.Shapes
If P.Type = msoLinkedPicture Then
If P.Left >= AC.Left And P.Left < AC.Left + AC.Width Then
If P.Top >= AC.Top And P.Top < AC.Top + AC.Height Then
P.Delete
Exit For
End If
End If
End If
Next P
End If
Set P = ActiveSheet.Shapes.AddPicture(PicFile, True, True, AC.Left, AC.Top, 200, 200)
ShowPicD = True
Exit Function
Done:
ShowPicD = False
End Function
Function PicExists(P As Shape) As Boolean
'Return true if P references an existing shape
Dim ShapeName As String
On Error GoTo NoPic
If P Is Nothing Then GoTo NoPic
ShapeName = P.Name
PicExists = True
NoPic:
PicExists = False
End Function
and try

=showpicd("c:\images\"&b2&".bmp")

where b2 houses the image name

HTH

danovkos
04-22-2009, 03:29 AM
Hi,
i like this idea and try to use it but it doesnt works for me.
what i did:
- create folder images on C: and insert there a picture 1.jpg
- copy this code to modules of sheet and in workbook too
- insert formula =showpicd("C:\images\"&B2&".jpg") in A2 and in B2 i write number 1 (this is name of my image)

can you help me?
thx

result: error:
#NAME?

Krishna Kumar
04-22-2009, 09:15 AM
Copy the code into a standard module rather than sheet/workbook module.

danovkos
04-22-2009, 10:32 PM
i did it, but it didnt help.
i copy the code to my personal modul ant the same error

JONvdHeyden
04-22-2009, 11:57 PM
Hello

I usually tend to add pictures to comments. Debra has a nice piece for sample code of how to add pictures in a list to comments in the adjacent cells. Link here: http://www.contextures.com/xlcomments03.html#PictureFile

HTH

danovkos
04-23-2009, 01:18 AM
this look good, but i need something, what will works like:
if this condition 1, show this pic, if = 2 this pic2 ...
but this code with comments i save for me too...maybe i will use it next time..

Krishna Kumar
04-24-2009, 07:41 AM
i did it, but it didnt help.
i copy the code to my personal modul ant the same error

See the attached.

HTH

danovkos
04-27-2009, 12:44 AM
yes, this works great, thank you.

and how can i do, that the source of picture will be picture, inserted in the same workbook, but other sheet? I mean, that source of picture will not from any path, but from the same workbook.

danovkos
04-30-2009, 04:35 AM
hi all,
can anyone tell me, how can i do, that the picture, which is inserted will be not locked?

i try use this example in my DB, but i need to have protected workbook. But if i do this, the code doesnt works good. It will appear the picture, but dont wont to dissaper when i have my wb protected.
If wb is not protected, it works good.
:(