Excel OnChange event

Question

I want to update a cell with the date-time when a value is entered to another one so,

How can i change the value of the B column to the date-time when a value is entered to A?

Is there something like, if-then-else?

I tried conditional formatting but with no success.

Could someone please post a working example?

Thank you

Answer 1

You could have tried this by yourself, couldn't you?
In my opinion, the hardest part was to think about the Change event.

Private Sub Worksheet_Change(ByVal Target As Range)    
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
Application.EnableEvents = False

ActiveSheet.Cells(Target.Row, 2).Value = Format(Now, "h:mm")
Application.EnableEvents = True
End Sub

You can change the Format content to whatever you need.

Answer 2

The formula =NOW() is volitile, meaning it will recalculate whenever anything in the workbook is changed so if you use that, you should have no problem.

On a seperate note, you can create UDFs that are volitile as well by simply adding Application.Volitile inside the code.

Answer 3

Try using

=IF(A3<>"",IF(B3="",NOW(),B3),"")

The values will be in column A and the time-stamp in column B. Also, you'll have to enable iterative calculations in the options.

Note that this will generate a time-stamp only when a value is first entered in Column A. To use this, the way you want, instead of normally editing a cell's value, you'll have to delete it and re-enter a new value.

I fear this is just a work-around and not a complete solution. I'll update this in case I find something more convenient.

Answer 4

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
    Application.EnableEvents = False
If Target.Cells.Column = 1 Then
        n = Target.Row
        If Me.Range("A" & n).Value <> "" Then
            Me.Range("B" & n).Value = Format(Now, "hh:mm:ss")
        End If
    End If
enditall:
    Application.EnableEvents = True
End Sub

How to use?

1. Right-click on the sheet tab and "View Code".

2. Paste the code into that sheet module.

3. Alt + q to return to the Excel window.

Click here for a sample file...

Answer 5

Here's an alternative event procedure that handles the case where the user updates more than one cell at a time (i.e. pastes a block of cells).

When working with the Worksheet_Change event procedure it is imperative to turn off event handling at the beginning and to make sure it always gets turned back on at the end.

Note that I intentionally left out the formatting of the time stamp as previous examples stripped off the date. If the column is not already formatted and you need to format it, I suggest you add a line of code to set the .NumberFormat property.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Column = 1 Then
        Target.Resize(ColumnSize:=1).Offset(ColumnOffset:=1).Value = Now
    End If
    Application.EnableEvents = True
End Sub

The case of tracking changes in Column A is easy; other columns can be a bit trickier when users paste blocks of cells. For example, if you want to capture changes to Column D, the code needs to handle the case when cells C2:D2 are pasted in which case Target.Column = 3. (Use the Intersect method or also look at the Target.Columns.Count property.