I developed and tested the code on Win 2000/Excel 2000, and tested it again
to verify it is working. Sorry I didn't clarify, though: the code snippets
are only the building blocks. Here is an entire macro that will illustrate
how it works (in this case I am using a button to create the picture and size
it, then a message box comes up and after clicking the picture source should
change - you can build your macro likewise by selecting the cell value from
your list):

Sub Button1_Click()
Dim NewPic As Shape, PicturePath1 as String, PicturePath2 as String
PicturePath1 = ' Insert path to 1st picture file
PicturePath2 = ' Insert path to 2nd picture file
With Range("A1")
.Width = 100
.Height = 66
End With
Set NewPic = AddPicture(Range("A1"), PicturePath1) ' Creates the picture in A1
MsgBox "PRESS OK TO CHANGE PICTURE:"
ChangePicture NewPic, PicturePath2 ' Changes the picture in A1
End Sub

Function AddPicture(PicCell As Range, PicFilePath As String) As Shape
Dim MyPic As Shape
With PicCell
Set MyPic = .Parent.Shapes.AddShape(msoShapeRectangle, .Left, .Top,
..Width, .Height)
End With
MyPic.Fill.UserPicture PicFilePath
Set AddPicture = MyPic
End Function

Sub ChangePicture(ByVal MyPic As Shape, NewPicFilePath As String)
MyPic.Fill.UserPicture (NewPicFilePath)
End Sub

Note that you can use AddPicture repeatedly to create multiple pictures on
the sheet, or use AddPicture once and then ChangePicture to use the same
rectangle but have the picture change. You could even create the rectangle
(as an autoshape) manually once and then use ChangePicture to dynamically
change it.

Not sure what application you have in mind, but suppose you have a database
list of employees with identifying info, including in one column a path to a
photo of them. You set up a form on a worksheet where the user types in an
employee number and (by using the macro in combination with lookup functions)
the picture changes as the employee info goes into other cells on the sheet.
Things like this are possible using this technique.
--
- K Dales


"[email protected]" wrote:

> I tried implementing your code, but when i run the macro nothing seems
> to happen. Is there something in the code that i need to tweak? Am i
> missing something?
>
>