VBA - Timestamp is not updating automatically after 1 day

[Dream World]

Hi there,

i have this file where i use it for my To Do list in my work. and after i set the code in the file it worked like a charm, after some time (usually 1 or 2 days) the code broke and when i fill a new item on my To Do list timestamp is not appearing or updating automatically anymore.

You can find the table below ..

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ITEM[/TD]
[TD]Time & Date (Started)[/TD]
[TD]Status[/TD]
[TD]Who[/TD]
[TD]Last Update[/TD]
[TD]Time & Date (Last Update)[/TD]
[TD]Next Action[/TD]
[TD]Follow up[/TD]
[TD]Action Needed[/TD]
[TD]Email Subject[/TD]
[TD]No. of Days passed from last update[/TD]
[TD]Duration[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


There are two timestamp in the code :
- (Time & Date (Started)): it will be updating only once when the ITEM is first created.
- (Time & Date (Last Update)): it will be updated every time there is a change in (Last Update) column.


VBA Code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)    Dim rCell As Range
    Dim rChange As Range
    
    On Error GoTo ErrHandler
    Set rChange = Intersect(Target, Range("A:A"))
    If Not rChange Is Nothing Then
        Application.EnableEvents = False
        For Each rCell In rChange
            If rCell > "" Then
                With rCell.Offset(0, 1)
                    .Value = Now
                    .NumberFormat = "dd/mm/yyyy hh:mm:ss"
                End With
            Else
                rCell.Offset(0, 1).Clear
            End If
        Next
    End If


    Set WorkRng = Intersect(Application.ActiveSheet.Range("E:E"), Target)
    xOffsetColumn = 1
    If Not WorkRng Is Nothing Then
        Application.EnableEvents = False
        For Each Rng In WorkRng
            If Not VBA.IsEmpty(Rng.Value) Then
                Rng.Offset(0, xOffsetColumn).Value = Now
                Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
            Else
                Rng.Offset(0, xOffsetColumn).ClearContents
            End If
        Next
        Application.EnableEvents = True
    End If
    
ExitHandler:
    Set rCell = Nothing
    Set rChange = Nothing
    Application.EnableEvents = True
    Exit Sub
ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub

 

[RoryA]

Have you restarted Excel? This is usually caused by some code disabling events and not re-enabling them.

 

[Dream World]

Yes tried restarting file, Excel. but no matter what i do it won't work anymore.
And this happened a couple of times, and each time i try to create the file from scratch in order for the code to work then this happen.

 

[RoryA]

If you open the VB Editor then go to the Immediate Window (Ctrl+G), and type:

?Application.enableevents

and press enter, does it return True or False?

 

[Dream World]

Returns nothing, only a message appeared saying "The Macros in this project are disabled". just found out when i checked (File > Options > Trust Center > Trust Center settings > Macro settings) that all macros option are disabled and cannot be modified.

Thanks for your help .. i appreciate it

 

[RoryA]

I guess that explains it then!