VBA Date and Time Stamp Issue

[JoRyBar]

Hello Gurus,

I am currently working on adding a date/time stamp when things are entered into a column. So for example, if I enter something in column O, I would like the date/time stamp in column P. This following code seems to work most of the time, but once in a while, it stops working and I get a debugger message. I believe the red font section is causing the issue. Any help would be greatly appreciated, since I am using this formula in multiple columns and sheets throughout the workbook.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("P:P, X:X"), 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
End Sub

Also, is there a way to make the date be alphanumeric like 10-Sep-2018? In advance, thank you.

Best
Novice

 

[mumps]

If you simply want a time stamp in column P when entering data in column O, this macro in the worksheet code module should do what you want in the format you requested.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("O:O")) Is Nothing Then Exit Sub
    Target.Offset(0, 1) = Format(Date, "dd-mmm-yyyy")
End Sub

 

[mumps]

This will also include the time.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("O:O")) Is Nothing Then Exit Sub
    Target.Offset(0, 1) = Format(Now(), "dd/mm/yyyy h:mm AM/PM")
End Sub

 

[JoRyBar]

Yay! This seems to work...thank you

I did want to ask about one thing - with the previous code, when I deleted the item from the previous column, the date would disappear too. How would I be able to add this to the above formula?

Thanks again!!

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("O:O")) Is Nothing Then Exit Sub
    If Target = "" Then
        Target.Offset(0, 1).ClearContents
    Else
        Target.Offset(0, 1) = Format(Now(), "dd/mm/yyyy h:mm AM/PM")
    End If
End Sub