Identifying an autoshape by row

[Steeviee]

Hello Gang,

I'm not even sure that this is possible but here goes.

I have a macro that, if a date that is late to schedule is entered, puts a nice big black arrow onto that line and formats some boxes to the right (where the arrow is pointing) to show that more data is required. All well and good.

I have been asked to now alter this so that when a late date is changed and so is now forecast to be on time, the boxes to the right disappear (pretty easy as it's just formatting) and the arrow disappears...

Now I'm in trouble. When I record macros to delete or hide the autoshape, it always refers to it by number...

ActiveSheet.Shapes("AutoShape 16").Select
    Selection.Delete

is there any way to refer to it by location? The macro is a Private Sub Worksheet_Change(ByVal Target As Range) type and I define the row number. Can I identify the shape that's located in this row to delete or hide? When I create the autoshape, can I name it referring to the row, maybe?

Many thanks in advance - I hope that you can help, even if if it's just to confirm my fear that it won't work.

 

[Steeviee]

Ignore me - I was thinking about naming the shape as I was writing the post. I now name the shape "Arr" & RR (where RR is my row number) when creating and I can then delete it no trouble ...

 

[MickG]

Just a thought:-
This code will Hide any shape that is in the same row as the row that is selected for change.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Shp As Shape
For Each Shp In ActiveSheet.Shapes
    If Target.Row = Shp.TopLeftCell.Row Then
        Shp.Visible = False
    Else
        Shp.Visible = True
    End If
Next Shp
End Sub

 

[Steeviee]

Thanks MickG. It's good to know that this can be done by location.

Much appreciated.