Vba Worksheet_Change event does not trigger when copy and paste data into column but works with a manual click into cell

Question

I am trying to solve an issue with a piece of code. I am aware this question has been asked before but i cannot get those solutions to work. The below worksheet change event does not trigger when i copy and paste data into column A but does when the user clicks into the cells manually how can i get round this?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range

 

   Application.EnableEvents = False

 

For Each cell In Target

If Not Application.Intersect(cell, Range("A7:A1048576")) Is Nothing Then

If Not IsNumeric(cell.Value) Then

    cell.Value = vbNullString

    MsgBox ("Please re-enter, value entered contains non-numeric entry")

End If

End If

Next cell

 

If Not Intersect(Target, Range("A7:A1048576")) Is Nothing Then

On Error Resume Next

If Target.Value = "" Or Target.Value = "0" Then

Target.Offset(0, 12) = ""

Target.Offset(0, 13) = ""

Else

Target.Offset(0, 12).Value = Format(Now, "mm/dd/yyyy HH:mm:ss")

Target.Offset(0, 13).Value = Environ("username")

 

End If

End If

    Application.EnableEvents = True

End Sub

Answer 1

This code should just about do what you want. Please try it.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Cell As Range

    If Not Application.Intersect(Target, Range("A7:A1048576")) Is Nothing Then
        Set Target = Target.Columns(1)          ' remove all cells outside column A
        Application.EnableEvents = False
        
        For Each Cell In Target.Cells
            With Cell
                If .Value = "" Or .Value = 0 Then
                    .Offset(0, 12).Resize(1, 2).Value = vbNullString
                Else
                    If Not IsNumeric(.Value) Then
                        .Value = vbNullString
                        MsgBox ("Please re-enter, value entered contains non-numeric entry")
                        .Select
                        Exit For
                    Else
                        .Offset(0, 12).Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
                        .Offset(0, 13).Value = Environ("username")
                    End If
                End If
            End With
        Next Cell
        
        Application.EnableEvents = True
    End If
End Sub