Worksheet change VBA Code not working

MFish

Board Regular
Joined
May 9, 2019
Messages
76
Code:

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!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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:
Code:
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.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox "Worksheet_Change code being called"
    ...
 
Last edited:
Upvote 0
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"?
 
Upvote 0
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:
Code:
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
 
Upvote 0
This works better. Haven't had issues, yet. Also, it seems like it computes faster. I doesn't have a "Lag" to the workbook.
 
Upvote 0
Excellent. Glad to to hear it!
:)
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top