stirlingmw1
Board Regular
- Joined
- Jun 17, 2016
- Messages
- 53
- Office Version
- 2016
- 2013
- 2010
- 2007
- Platform
- Windows
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.
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
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.
VBA Code:
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
VBA Code:
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