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?

 

[DauntingGecko]

[imageName].Delete

 

[stefankemp]

Are there any other shapes on the "HOME PAGE" sheet?

 

[lpking2005]

Yes, there is other images,
one problem of the problems is, the pictures have a name, but when they are pasted into other workbook, they dont keep their original name.

 

[stefankemp]

After your line

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

insert the following:

selection.name = "Some unique string"

That way the picture will always have the same unique name, so when you want to delete it you can just do

Sheets("HOME PAGE").shapes("Some unique string").delete.

 

[lpking2005]

After your line

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

insert the following:

selection.name = "Some unique string"

That way the picture will always have the same unique name, so when you want to delete it you can just do

Sheets("HOME PAGE").shapes("Some unique string").delete.

Will this cause a problem with pasting different images with different names each time?

 

[lpking2005]

Great! that worked! Thanks.
Just one more problem,

Does anybody know where and what code to input into the code below, to be able to have the picture delete when Cell (J21) is empty.
Many Thanks!



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 = 311
.Width = 111
.Copy
End With

ThisWorkbook.Activate

Sheets("HOME PAGE").Range("Q27").PasteSpecial
Selection.Name = "34002932"
Sheets("HOME PAGE").Shapes("34002932").Delete

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

Else
'delete shape
End If

End If

 

[lpking2005]

Anyone know this please?

 

[stefankemp]

Replace

'delete shape

with

Sheets("HOME PAGE").shapes("Some unique string").delete

 

[lpking2005]

Replace

'delete shape

with

Sheets("HOME PAGE").shapes("Some unique string").delete

thanks but its still not working, this is really bugging me now.
It seems like its so simple.