Worksheet change VBA Code not working

[MFish]

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D2:D10000")) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Target
    .Offset(0, -2).Value = Date
    .Offset(0, -1).Value = time
    .Offset(0, -3).Value = Environ("Username")

End With
Application.EnableEvents = True

End Sub

I don't understand how this doesn't work.... If there is ANY change within the range of D2:D10000 then it should put the Time, Date, and Username within the cells to the left of it... What's weird is that SOMETIMES it works... Then you save the workbook... Reopen it and then it doesn't. Yes, macros are enabled. Help!

 

[Joe4]

How exactly are the values in column D being changed?
Are you only changing one cell at a time in column D?

Then you save the workbook... Reopen it and then it doesn't. Yes, macros are enabled. Help!

Are you closing out of Excel entirely, or just the workbook?
If you still have your Excel session open and are just re-opening the workbook, I suspect that your Events are disabled. This can happen if you encounter some error where the code runs the "Application.EnableEvents = False" line, but never gets to the companion "Application.EnableEvents = True" line.

Try manually running this code and see if it works after that:

Sub FixIx()
    Application.EnableEvents = True
End Sub

You can also temporarily place a MsgBox at the very beginning of your code to verify that it is being triggered, i.e.

Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox "Worksheet_Change code being called"
    ...

 

[MFish]

Hi Joe4,

My values in column D will be from Sheet1 where I delete rows from there and copy to sheet2 (Sheet2 is where this code is located). Basically it keeps a hidden recording of who deletes rows of information on sheet1 and I know who "to go after" should I have questions on their decision. The information is about 60-80 columns long, to the right of column D. But it will ALWAYS put something in Column D so that's why I have it only coded to this column.

Secondly, I have tried saving and closing just this workbook. Out of excel completely and I get the same weird sub-par results. Sometimes it works and sometimes it doesn't. Should I just never have the "Application.EnableEvents = False" code in there then? Always have it to be "= True"?

 

[Joe4]

Should I just never have the "Application.EnableEvents = False" code in there then? Always have it to be "= True"?

That code is often important if you are checking the same range that you are updating (to avoid getting caught in an endless loop as the code keeps calling itself).
But it should not be necessary here, as you are looking in column D, but updating columns A, B, and C.

I think the bigger issue is that you may be copying multiple rows at once, and the code is really only written to handle a single cell being updated.
Try this variation and see if it works better:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rng As Range, cell As Range

    Set rng = Intersect(Target, Range("D2:D10000"))
    
    If rng Is Nothing Then
        Exit Sub
    Else
        For Each cell In rng
            With cell
                .Offset(0, -2).Value = Date
                .Offset(0, -1).Value = Time
                .Offset(0, -3).Value = Environ("Username")
            End With
        Next cell
    End If

End Sub

 

[MFish]

This works better. Haven't had issues, yet. Also, it seems like it computes faster. I doesn't have a "Lag" to the workbook.

 

[Joe4]

Excellent. Glad to to hear it!