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

score 2 · Answer 1 · answered Dec 14 '11 at 02:27

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.

+1 good point on the multi-cell update, too often overlooked. — brettdj, Dec 14 '11 at 11:52

JMax · Answer 2 · 2011-12-14T12:01:21.657

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.

edited Dec 14 '11 at 12:01

answered Dec 13 '11 at 20:19

JMax

26,109
12
69
88

Hello, yes i definitely could, i'm a programmer for 10 years but really don't know to use excel, nor am i the greatest with math actually :) I don't even know where i am going to add this code to but i will try it. Thank you :) – Herr Dec 13 '11 at 20:43
Sorry, I may have misunderstood your title... What I've shown is an [Event Procedure](http://www.cpearson.com/excel/Events.aspx), you need to add this code to the worksheet: right-click on the tab and click on `Edit code` and copy-paste the code from my answer (have a look to the link above in my comment too) – JMax Dec 13 '11 at 20:48
2

+1 - but suggest you move your EnableEvents = False to line 3 after your Intersect test :) – brettdj Dec 14 '11 at 11:32

score 1 · Accepted Answer · answered Dec 13 '11 at 20:19

1

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.

answered Dec 13 '11 at 20:19

kshenoy

1,726
17
31

Thank you ronin, i will definitely try this :) – Herr Dec 13 '11 at 20:44
1

This will work but may be recalculated each time every formula is recalculated (on opening for instance...) - I have to check this though – JMax Dec 13 '11 at 20:46
Since you seem to be interested in the edit history, try [this one](http://www.tlookup.com/) out – kshenoy Dec 17 '11 at 08:33

Kannan Suresh · Answer 4 · 2011-12-15T11:07:42.280

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?

Right-click on the sheet tab and "View Code".
Paste the code into that sheet module.
Alt + q to return to the Excel window.

Click here for a sample file...

score 0 · Answer 5 · answered Dec 13 '11 at 22:29

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.

Excel OnChange event

5 Answers5

Linked