delete picture problem

[lpking2005]

hi, currently i have some code which copy/pastes an image from a workbook into another workbook when I input a order number into Cell (J21)

The problem is that when I insert a different order number, all it does is paste another image on top of the existing image.

I need it to delete the existing picture first before receiving new image and delete the existing picture when Cell (J21) is empty.

Here is the code i am using:

Private Sub Worksheet_Change(ByVal Target As Range)
'paste image from matrix

If Not Intersect(Target, Range("J21", "K22")) Is Nothing Then

If Range("J21").Value <> "" Then
SKU = Range("M23").Value

Workbooks.Open "T:\Production\PROD MOULDING\2 - LINE MANUAL PROJECT\Mouldings Product_Labour Matrix.xls", 0, 1

With Sheets("403").Shapes("" & SKU & "")
.Height = 301
.Width = 101
.Copy
End With

ThisWorkbook.Activate

Sheets("HOME PAGE").Range("Q27").PasteSpecial

With Application
.DisplayAlerts = False
Windows("Mouldings Product_Labour Matrix.xls").Close
.DisplayAlerts = True
End With

Else
'delete shape
End If

End If

End Sub

any help please?

 

[stefankemp]

It will only work once you have first copied a picture into the sheet and named it "Some unique string".

 

[lpking2005]

i have done this

 

[NdNoviceHlp]

Why are you opening another wb? Why use the ws change event? It's seems fairly straight forward... name the shape consistantly. Before pasting the pic, see if it exists, if it does then delete it. Something like this (untested). HTH. Dave

Dim Sh As Shape
If Sheets("HOME PAGE").Range("J21").Value <> "" Then
With Sheets("HOME PAGE")
For Each Sh In .Shapes
If Sh.Name = "34002932" Then
Sh.Delete
Exit For
End If
Next Sh
End With
Sheets("HOME PAGE").Range("Q27").PasteSpecial
Selection.Name = "34002932"
End If