Pictures in Excel

Can you get the pictures to display ok in cell A1? In relation to the drop down list?

Is it just the positioning of the picture you need help with?

Hi R1chard,
Thanks for your reply.

I successfully did this some time ago but only on a new spreadsheet. However, when I used it on my existing sheet it screwed up badly and I abandoned the attempt. No matter what I tried it would not work. I did save all my workings but a hard drive crash caused me to lose my working data and I had not taken a back up. I had already given up in frustation though and now having reconstructed my sheet would like to revisit this option.
To be honest, I've forgotten how to do it and I was hoping someone has already done this and could help with the steps needed. I remember previously I had this documented on my sheet but it must have been incorrect as it was flaky in operation.

I remember my pictures were disappearing and the sizing at A1 was an issue. And I couldn't find the pictures again to change the size.

If you could help with anything, I would like to study the steps and work out what may have been wrong. Perhaps I will come to the same result as before and give up. But for now I can't remember.

Cheers
Rick

R1chard said:

Can you get the pictures to display ok in cell A1? In relation to the drop down list?

Is it just the positioning of the picture you need help with?

Click to expand...

Hi Rick,

Click the following post. It might help you..

http://www.mrexcel.com/board2/viewtopic.php?t=104196&highlight=

Kris

Here's a User Defined Function I picked up from an MVP, Damon

If I were you I would store your pictures in a file. For example, Save: Aircraft.jpg, Trains.jpg, and Ships.jpg in a folder C:\Excel Pictures\"
You might make G3 = "C:\Excel Pictures\" & F3 & ".jpg"
Make Cell A1 =ShowPicD(G3)

Now the picture should change whenever you change F3.
The pictures will all be forced to 200 x 200 pixels, but you can change that in the code. Another nice feature of this function is that the picture will always show up in the cell that calls the function.


Damon made my day when he gave me this function. I use it to put my students pictures on my seating chart. When I mix up the names on the chart, the corresponding student picture changes accordingly.



Function ShowPicD(PicFile As String) As Boolean
'Same as ShowPic except deletes previous picture when picfile changes
Dim AC As Range
Static P As Shape
Dim VShft As Integer, HShft As Integer
VShft = Range("W6") 'amount to shift picture up or down
HShft = Range("W5") ' amount to shift picture left or right
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 + HShft And P.Left < AC.Left + HShft + AC.Width Then
If P.Top >= AC.Top + VShft And P.Top < AC.Top + VShft + AC.Height Then
P.Delete
Exit For
End If
End If
End If
Next P
End If
Set P = ActiveSheet.Shapes.AddPicture(PicFile, True, False, AC.Left + HShft, AC.Top + VShft, 200,200) '200 x 200 pixels, change to desired size
P.Select
Selection.ShapeRange.ZOrder msoBringToFront
Selection.ShapeRange.Shadow.Visible = msoFalse
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

Here's the original Post
Note: ShowPicD is a better function than ShowPic, because it deletes the old picture each time. Rather than create a huge stack of pictures.
http://www.mrexcel.com/board2/viewtopic.php?t=104322&highlight=

Thankyou all for your excellent responses. You have been most helpful.
Regards,
Rick