Altering code from Worksheet_Change so that Undo works

[michaeltsmith93]

Hi,

I posted a question about this earlier, but have discovered a little bit more on my own and have run into a different problem. With the following code, the Undo function is effectively removed. Do I have any options for replicating this code but not making it a worksheet change dependent thing and thereby ruining Undo?

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Dim intersection As Range
    Set intersection = Application.Intersect(Target, Me.Range("A:K"))
    If Not intersection Is Nothing Then
        Dim r As Range
        For Each r In intersection.Rows
            r.EntireRow.Range("M1").Value = Date
            r.EntireRow.Range("L1").Value = Application.Username
        Next
    End If
    Application.EnableEvents = True
End Sub

 

[Fluff]

You loose the undo stack when any macro makes a change to the sheet, not just change events

 

[Jaafar Tribak]

If you are editing one cell at a time in column AK then you could probably use sendKeys to preserve the undo stack