VBA - copy, paste, copy new & delete old

[gino59]

Hi all!

I'm trying to figure out how to delete a pasted image when the user selection changes.

Step 1 is the user selects a country from a combobox.
Step 2 - the code copies the image associated with that country.
Step 3 - the code pastes the selected image to the sheet.
Step 4 - the user selects a different country from the combobox.
Step 5 - the code copies the new image and pastes it over the first image.

Problem is - how do I delete the first pasted image and leave only the second (or third, fourth, etc.)??

So the combobox calls this module...

Public Sub copy_flag()
            Selection.Copy
            Sheets("sheet1").Activate
            ActiveSheet.Shapes.Range(Array("Rectangle 6")).Select
                ActiveSheet.Paste
                Selection.ShapeRange.IncrementLeft 66
                Selection.ShapeRange.IncrementTop 33
            ActiveSheet.Shapes.Range(Array("combobox1")).Select
End Sub

As you can imagine, the images just pile up on top of each other! Any ideas how I can delete the first image AFTER the second image gets pasted?

Many many thanks as always!!

Cheers,
Gino

 

[jbeaucaire]

Perhaps if you take control of what the resulting pasted shape is named, you can just keep deleting and a pasting/renaming based on that one temporary name? Something like this... pseudo code, meant to get you thinking, testing.

Public Sub copy_flag()
            
    Selection.Copy
    Sheets("sheet1").Activate
    ActiveSheet.Shape("TempShape").Delete
    ActiveSheet.Shapes.Range(Array("Rectangle 6")).Select
        ActiveSheet.Paste
        Selection.ShapeRange.IncrementLeft 66
        Selection.ShapeRange.IncrementTop 33
        Selection.ShapeRange.Name = "TempShape"
    ActiveSheet.Shapes.Range(Array("combobox1")).Select

End Sub

 

[gino59]

Thanks, jbeaucaire! Just what I needed! One small fix required and it's working like a charm!

Many thanks!

Cheers,
Gino

Public Sub copy_flag()
            Selection.Copy
            Sheets("sheet1").Activate
            ActiveSheet.Shape[COLOR=red][B]s[/B][/COLOR]("TempShape").Delete
            ActiveSheet.Shapes.Range(Array("Rectangle 6")).Select
                ActiveSheet.Paste
                Selection.ShapeRange.IncrementLeft 159.4
                Selection.ShapeRange.IncrementTop 7.4
                Selection.ShapeRange.Name = "TempShape"
            ActiveSheet.Shapes.Range(Array("combobox1")).Select
End Sub