Delete shape before inserting new one ?

[fredrerik84]

Hi I started an quite similar thread earlier today but it was a little confusing so I have a better question this time around.

im working on a code (still on test bench) but here is what I got:

Sub shape2017()
Dim LogoPath As String
Dim j As Long
Dim ws As Worksheet: Set ws = Worksheets("Data")
j = 51

LogoPath = ThisWorkbook.Path & "\Icons\" & Cells(j, "G") & ".png"

With ActiveSheet.Pictures.Insert(LogoPath)
    With .ShapeRange
        .LockAspectRatio = False
         .Width = 30
         .Height = 18
    End With
    .Left = ActiveSheet.Cells(653, 6).Left + 1
    .Top = ActiveSheet.Cells(653, 6).Top - 0.5
    .Placement = 1
    .PrintObject = True
End With
End Sub

So this works as expected, but Cells(653, 6) already has an img from before. How can I delete this shape before inserting a new one ?
, and the same cell also has a dropdown menu.

anyone know a way to handle this ? would be much appreciated

 

[tonyyy]

frederik84,

You might consider the following...

Sub shape2017()
Dim shp As Shape
Dim LogoPath As String
Dim j As Long
Dim ws As Worksheet: Set ws = Worksheets("Data")
j = 51

For Each shp In ActiveSheet.Shapes
    If shp.Left = ActiveSheet.Cells(653, 6).Left + 1 _
         And shp.Top = ActiveSheet.Cells(653, 6).Top - 0.5 Then shp.Delete
    Exit For
Next shp

LogoPath = ThisWorkbook.Path & "\Icons\" & Cells(j, "G") & ".png"

With ActiveSheet.Pictures.Insert(LogoPath)
    With .ShapeRange
        .LockAspectRatio = False
         .Width = 30
         .Height = 18
    End With
    .Left = ActiveSheet.Cells(653, 6).Left + 1
    .Top = ActiveSheet.Cells(653, 6).Top - 0.5
    .Placement = 1
    .PrintObject = True
End With
End Sub

Cheers,

tonyyy

 

[fredrerik84]

Hi, thanks for your reply. This seams correct but for some reason its not working. I think maybe the problems is because ive offset the shape from top, left here is what ive tried for debugging :

For Each shp In ActiveSheet.Shapes
    If shp.Left = ActiveSheet.Cells(653, 6).Left + 1 _
         And shp.Top = ActiveSheet.Cells(653, 6).Top - 0.5 Then
            shp.Delete
            MsgBox "found"
        End If
    Exit For
Next shp

 

[fredrerik84]

Never mind the exit for messed it up.

corrected to this:

For Each shp In ActiveSheet.Shapes
    If shp.Left = ActiveSheet.Cells(653, 6).Left + 1 _
       And shp.Top = ActiveSheet.Cells(653, 6).Top - 0.5 Then
          shp.Delete
          Exit For
    End If
Next shp

finally its working . , thanks! you are the best , cheers

 

[tonyyy]

You're welcome. Glad you got it working...