Need Help with auto insert of pictures

[Bradley Wolosz]

Hello all,

I have an excel work sheet developed
I would like to add a function that will insert a picture into a cell
based on a second cell's value
The second cell value would determined from a list located on a second
sheet

I have about 20 pictures that would cross referance 20 values in the
list
I would like to store these pictures on the second sheet with the
list's

I would like this function to work in 5 different areas on the sheet
all 5 areas would use the same list & same pictures
I would also need to be able to copy the main sheet to create create
additional copys of the main sheets lay-out

Can this be done
If so could you please provide me with some information how to do this
If needed I can e-mail my work sheet & gif pictures

Any help would be greatly appreciated

Thanks

 

[Bernie Deitrick]

Visit

http://www.contextures.on.ca/excelfiles.html#DataVal

and download ClipArtEvent.zip

for some examples of using pictures in a file.

HTH,
Bernie
MS Excel MVP

 

[Bradley Wolosz]

Bernie,

Thanks for the help this works great,
By any chance would you know how to alter this code so i can place the
pictures on a sheet with a specific name
then call that sheet to cross referance the pictures

 

[Bernie Deitrick]

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count <> 1 Then Exit Sub

If Intersect(Target, Range("KeyCells")) Is Nothing Then
Exit Sub
Else
On Error Resume Next
ActiveSheet.Shapes(Target.Address & "Final").Delete
End If

Sheets("SheetWithPictures").Shapes(Target.Value).Copy
Target.Offset(0, 1).Select
ActiveSheet.Paste
Selection.Name = Target.Address & "Final"
Selection.ShapeRange.ZOrder msoSendToBack
Target.Select

End Sub


HTH,
Bernie
MS Excel MVP