Passing Worksheet_Change Target.Row through OnAction

[stirlingmw1]

Afternoon
I have a piece of code that adds a Picture to a cell if text is present in column C of that same row and renames the pic to "Tick" & Target.Row.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim shp As Shape
If Not Intersect(Target, Range("C:C")) Is Nothing Then
        If Range("C" & Target.Row).Value <> "" Then

For Each shp In ActiveSheet.Shapes
If shp.Name = "Tick" & Target.Row Then shp.Delete
Next shp
    ActiveSheet.Shapes.Range(Array("Picture 8")).Select
    Selection.Copy
    Range("I" & Target.Row).Select
    ActiveSheet.Paste
    Selection.ShapeRange.IncrementLeft 15.75
    Selection.ShapeRange.Name = "Tick" & Target.Row
    ActiveSheet.Shapes("Tick" & Target.Row).OnAction = "Duplicate"
    Range("D" & Target.Row).Select
       End If
       End If

Sub Duplicate(ByVal Target)
    ActiveSheet.Range("B" & Target.Row, "F" & Target.Row).Select
    
End Sub

I am trying to add a macro to this picture so if the picture is pressed (OnAction) it copies the range ("B" & Target.row", "F" & Target.row) to the next row down.
the problem I am having is passing the Target.Row from the Worksheet_Change Sub to the Duplicate Sub.

Any ideas what I am doing wrong or not doing?

Thanks

Steve

 

[RoryA]

You're not passing the row at all. You shouldn't really need to though if the pictures are lined up with the rows in question. In the Duplicate sub you can refer to Activesheet.shapes(application.caller).topleftcell.row to get the row number you need.

 

[stirlingmw1]

You're not passing the row at all. You shouldn't really need to though if the pictures are lined up with the rows in question. In the Duplicate sub you can refer to Activesheet.shapes(application.caller).topleftcell.row to get the row number you need.

RoryA
Thank you, with your advice and a little tweak it works a treat now.

Sub Duplicate()
    Dim RowNo As String
    RowNo = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row
    ActiveSheet.Range("B" & RowNo + 1, "F" & RowNo + 1).Value = ActiveSheet.Range("B" & RowNo, "F" & RowNo).Value
End Sub

Regards

Steve

 

[RoryA]

RowNo should really be a Long, rather than a String, although it shouldn't matter here.