vba add shapes (stars) in function of an excel cell's integer

Question

I have a column "I" on a spreadsheet that gives an int from 1 to 5. In function of the integer/ranking over 5, I manage to display stars within the cell next to the integer:

Dim x As Integer, Cel As Range, Plg As Range
Dim y As Integer, etoile As Shape
Dim shp As Shape

With mysheet
    Set Plg = .Range("I" & startLine & ":I" & nbLines)

    For Each Cel In Plg
        y = 5
        For x = 1 To Cel.Value
            If Cel > 0 Then
        Set stars= .Shapes.AddShape(msoShape5pointStar, Cel.Left + y, Cel.Top + 5, 6, 6)
        y = y + 10
        stars.Line.Visible = msoFalse
        stars.Fill.ForeColor.SchemeColor = 13
            End If
        Next x
    Next Cel
End With

But as I use a program that refresh my column I and the integer I'm looking for a way to remove the stars I created before and then applied the new ranking/number of stars.

I tried stuff like that before my code:

For Each shp In mysheet.Shapes
    If shp.Type = msoShapeTypeMixed Then shp.Delete
Next shp

Without great success... Any help would be appreciated !

Answer 1

Try something like this, please:

   Dim sh As Worksheet, st As Shape
   Set sh = ActiveSheet
     For Each st In sh.Shapes
        If st.Type = 1 And left(st.Name, 7) = "5-Point" Then st.Delete
     Next

You can also name the stars when created.Something like

'your existing code
'
Set stars= .Shapes.AddShape(msoShape5pointStar, Cel.Left + y, Cel.Top + 5, 6, 6)
       y = y + 10
 stars.Name = "MyStar" & "somethin else" '(maybe the Cel.Addres and use it in a similar way to delete only specific ones)
'your existing code

Then delete the shapes having the first 6 characters like "MyStar", or whatever you like...

If you need to selectively delete them, you can also determine their TopLeftCell address and delete according to this one.