Home » Blogs » Nick's blog

15. VBA Tips - Track Changes By Adding A Time Stamp

Nick's picture
Submitted by Nick on 12 May, 2009 - 10:22


Using VBA Events, there's a clever way to add a time stamp when cells change so that you don't need to remember whether you have updated it or not:

  • To do this, we can use the Worksheet_Change Event
  • We can pick up the event of a change in value in a column, and add the date and time in another 

Here's a screen shot of our data in Excel:

track-changes-by-adding-a-time-stamp

So, every time we change a value in the "Number1" column, a time stamp is updated in the "Last Changed" column

Here's the code we need to use in the worksheet module:

track-changes-by-adding-a-time-stamp

Explanation

  1. Worksheet_Change is an event that is fired off when a cell's value is changed
  2. Target in this case means the cell that has been changed
  3. We then check that we're changing data in the 4th column
  4. Then we add the result of the NOW function to the cell in the same row, but in column 3

Download sheet to practise how to Track Changes By Adding A Time Stamp in Excel

Training Video on how to Track Changes By Adding A Time Stamp in Excel:

AttachmentSize
track-changes-by-adding-a-time-stamp.xls34 KB
track-changes-by-adding-a-time-stamp-1.xls34.5 KB
»

Time Stamp

Submitted by chekeelas on 4 May, 2015 - 21:02.

I want to add a time stamp in one cell whenever any of multiple cells change. For example, if any cell in the range of columns E-Q changes, I want a time stamp is cell B2. Is this possible?

Thanks
Chekeela

»

Anyway of doing it for just a change in value if a formula?

Submitted by Anonymous on 4 May, 2012 - 15:36.

Thank you for the post. I was testing this code out by changing it to a formula instead. If the output from the formula does change, the date doesn't change. Is there anyway of changing the code record the date when the value changes without the formula changing?

»

not easily.. happy to look

Submitted by Anonymous on 6 May, 2012 - 16:29.

not easily..
happy to look into your request further

http://www.excelexperts.com/contact
if you'd like a quote.

Nick

»

This is almost what I need.

Submitted by Anonymous on 9 April, 2012 - 21:18.

I have the time stamp working, but I would like to do a conditional time stamp.
ie 'if cell a1 = "YES" then time stamp. 'if a1 = "NO" then dont time stamp.

is that possible?

»

Found it!

Submitted by Anonymous on 10 April, 2012 - 04:05.

this helped me out http://www.youtube.com/watch?v=rK8sW4MoG7Q

»

Multiple 'target' columns

Submitted by Anonymous on 1 January, 2012 - 07:24.

First of all, this is a very useful forum!

I need to track time changes in 2 or more columns and hopefully the reported static time stamps can show up in different cells.

Thank you for all your efforts to help and educate your community!

»
Nick's picture

the 2nd example file does

Submitted by Nick on 1 January, 2012 - 11:11.

the 2nd example file does what ur after

»

How do extend this code to include more columns?

Submitted by Anonymous on 23 November, 2011 - 15:47.

Hi Nick,

This is a really useful clip and very close to what I am trying to achieve. However, as a bit of a VBA beginner I'm hoping you can help me to understand how to modify your code slightly to truly meet my need.
Firstly I would like to extend the range of cells where a change could occur to include multiple columns instead of just one. Unfortunately my rather simplistic approach of just replicating the code for each additional column i want to include doesn't appear to work.
Secondly, is there any way of also having the sheet identify the user that is making the change at the same time?

Thanks

Neil

»
Nick's picture

try this

Submitted by Nick on 23 November, 2011 - 16:40.

try this

»

thanks for this, very helpful

Submitted by Anonymous on 1 December, 2011 - 09:42.

thanks for this, very helpful indeed.

Neil

»