Worksheet_Change Preventing Rows from being added

Question

I am far below novice when it comes to programming as in I find VBA code online and attempt to reconfigure it for my purposes.

I'm using the below code to capture a time stamp and username when certain cells are changed, which works perfectly, the problem comes when I attempt to add or delete rows.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    Dim ThisRow As Long ' make sure to declare all the variables and appropriate types
    ThisRow = Target.Row

    If Target.Column >= 1 And Target.Column <= 1 Then

        Dim sOld As String, sNew As String
        sNew = Target.Value 'capture new value

        With Application
            .EnableEvents = False
            .Undo
        End With

        sOld = Target.Value 'capture old value
        Target.Value = sNew 'reset new value

        If sOld <> sNew Then

            ' time stamp & username corresponding to cell's last update
            Range("L" & ThisRow).Value = Now & Environ("username")
            Range("L:L").EntireColumn.AutoFit

        End If

        Application.EnableEvents = True

    End If
End Sub

A Run-Time Error '13':

Type Mismatch window pops up. If I hit end the row is added or deleted, but if I add On Error Resume Next to the code, the pop-up doesn't pop, but the row also doesn't get deleted or added.

Any way to fix this? Or am I doomed to press end everytime I need to add or delete a row?

Edit: Forgot to mention that Debug highlights sNew = Target.Value 'capture new value as the problem.

Answer 1

Target can be a multi-cell range (as when a new row is added), so in those cases you can't assign its value to a String-type variable.

You can screen-out full-column changes though:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Target.Columns.Count = Me.Columns.Count Then Exit Sub 

    'or even 
    'If Target.Cells.Count > 1 Then Exit Sub 

    'rest of code here