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
»

Hi Nick, I've just noticed an

Submitted by Anonymous on 1 December, 2011 - 14:40.

Hi Nick,

I've just noticed an unexpected quirk of this code. It seems that the standard undo functionality within Excel no longer registers that a change has been made in one of the cells. All i did was a basic check that the code was working by changing the cell data in each of the columns i want to track changes in. Whilst the code definitely works what i'm finding is that i now can't undo the changes i just made which doesn't seem right. Any ideas why this might be happening?

Thanks

Neil

»
Nick's picture

unfortunately, when you run

Submitted by Nick on 1 December, 2011 - 15:59.

unfortunately, when you run VBA code that makes changes to the sheet, Excel loses it's undo..

»

Evolved requirement

Submitted by Anonymous on 13 January, 2012 - 15:51.

Hi Nick,

You kindly helped me with my time stamping question before Christmas. Unfortunately the no 'undo' issue i discovered eventually meant I couldn't implement the code as i'd hoped because the users wouldn't have been happy with the undo not working. However, I've now reworked the sheet (a sales pipeline) and would like to include a simplified time stamp. I'm looking to add a script that simply enters the date when an opportunity is closed as either 'won' or 'lost'. All the opportunities are entered from row 8 onwards with Column E being where the won/lost status is held and column P where I would like to add the date stamp. The one challenge I see is how to get the date to go away if, say, an opportunity originally closed as lost suddenly comes back to life?

Any help greatly appreciated.

Thanks

Neil

»
Nick's picture

hi NeilI've just written a

Submitted by Nick on 13 January, 2012 - 17:21.

hi Neil

I've just written a Sales pipeline system... you will encounter all kind of logic problems if you proceed this way.. the best way to do this is to have a proper system to enter sales opportunities, and save results. Take a look at the "share trading system" for an example of how this shld be done. If you have budget, we can write something amazing for your sales team

Nick

Request a Quote for more help

»