Automatic Date and Time get refreshed on pressing "Delete" Key in Excel

I am using a simple code to enter date and time automatically in 2 separate cells in the excel sheet, however, they change automatically if I enter a new value in the cell or just press "Delete" Key. Below is the code I am using:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 5 Then Exit Sub
Application.EnableEvents = False
Target.Offset(0, -2).Value = Date
Application.EnableEvents = True
If Target.Column <> 5 Then Exit Sub
Application.EnableEvents = False
Target.Offset(0, -1).Value = Time
Application.EnableEvents = True
End Sub

I need the date and time to remain static until I delete them from their respective cells. How can I achieve this?

Answers


This will preserve the date/time once they have been entered:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 5 Then Exit Sub
    Application.EnableEvents = False
        If Target.Offset(0, -2).Value = "" And Target.Offset(0, -2).Value = "" Then
            Target.Offset(0, -2).Value = Date
            Target.Offset(0, -1).Value = Time
        End If
    Application.EnableEvents = True
End Sub

EDIT#1:

This version will allow you to both set and clear multiple cells in column E:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, i1 As Long, i2 As Long

    If Target.Column <> 5 Then Exit Sub

    With ActiveSheet.UsedRange
        i2 = .Rows.Count + .Row - 1
        i1 = .Row
    End With

    Application.EnableEvents = False
        For Each r In Intersect(Target, Range("E" & i1 & ":E" & i2))
            If r.Offset(0, -2).Value = "" And r.Offset(0, -1).Value = "" And r.Value <> "" Then
                r.Offset(0, -2).Value = Date
                r.Offset(0, -1).Value = Time
            End If
        Next r
    Application.EnableEvents = True
End Sub

Clearing a cell that is already empty will not cause a time/date recording.


Stepping through your code:

 Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Column <> 5 Then Exit Sub

"If the target's column is not 5 then exit the subroutine" This is cool.

    Application.EnableEvents = False

Flipping this to false insures that this code won't run again until this value is set to true. Worksheet_Change needs enableevents to be on. So now if the cell that changed was in Column E then Worksheet_Change will be kept from executing again. This makes sense to keep infinite loops from happening as cells are changed via this code.

    Target.Offset(0, -2).Value = Date

Set the cell that is two columns back from the target cell to the current date.

    Application.EnableEvents = True

Set enableEvents back on. This is good since you probably don't want to leave this off.

    If Target.Column <> 5 Then Exit Sub

Why are we checking this again? Target.Column hasn't changed since last time, and if it was already <>5 then we wouldn't be here to test it. This line is superfluous.

    Application.EnableEvents = False

OK.. Well we just turned this on, but now we are turning this off again. Just leave it off.

    Target.Offset(0, -1).Value = Time

Set the value 1 column to the left of the target cell to the current time. Coolios.

     Application.EnableEvents = True

Turn enableEvents back on. This makes sense here.

End Sub

Rewriting this to remove the redundant toggles and superflous target.Column check:

Private Sub Worksheet_Change(ByVal Target As Range)
    'make sure this is column 5 that was changed. Like if anything changed in 
    ' column 5, then run the rest of this.
    If Target.Column <> 5 Then Exit Sub

    'Make sure we don't infinite loop if we accidently trigger a change to
    ' column 5 in this code.
    Application.EnableEvents = False

    ' Set two cells to the left to the current date
    ' and one cell to the left to the current time
    Target.Offset(0, -2).Value = Date
    Target.Offset(0, -1).Value = Time

    'turn events back on.
    Application.EnableEvents = True

 End Sub

So.. Everytime you make a change in Column 5, the date and time change. IF you want it so that it only changes a row's date and time once. Then check to see if date and time are already set for the row:

Private Sub Worksheet_Change(ByVal Target As Range)
    'make sure this is column 5 that was changed. Like if anything changed in 
    ' column 5, then run the rest of this.
    If Target.Column <> 5 Then Exit Sub

    'Check to see if the date and time are already set for this row:
    ' If they are, then exit subroutine.
    If target.offset(0,-2).value <> "" OR target.offset(0,-1).value <> "" Then Exit Sub

    'Make sure we don't infinite loop if we accidently trigger a change to
    ' column 5 in this code.
    Application.EnableEvents = False

    ' Set two cells to the left to the current date
    ' and one cell to the left to the current time
    Target.Offset(0, -2).Value = Date
    Target.Offset(0, -1).Value = Time

    'turn events back on.
    Application.EnableEvents = True
End Sub

Need Your Help

are MySQL INSERT statements slower in huge tables?

mysql optimization

I can see how SELECT and UPDATE statements get slower as a table grows and grows, but what about INSERT ?

In the .NET framework, why are there PointF (float) and no "PointD" (double)?

.net graphics floating-point double system.drawing

Can anyone explain why, in the .NET framework, there are PointF structures (using the single-precision float type) and no "PointD" (using the double-precision double type)?