00:00Here's a very personal question I have to ask. Have you ever had to deal with changes someone
00:04made in your Excel spreadsheet but there was no way to track what was changed and most important
00:10of you all, when was it changed? Well, enter the Excel change log feature and all it requires
00:17are this simple VBA script. Start off by right clicking on your sheet and go view code. In here,
00:23you're going to paste this VBA code. If you do require a copy with this VBA script, kindly please
00:28refer requests on the comment section of this video and I'll send it to you. Let's dissect this
00:33VBA script a bit here. There's basically two subroutines here. One of them is called worksheet
00:38change which get executed when you make any cell value changes here and then another one is called
00:43worksheet selection change so that when you select a particular cell, this will get executed. The
00:49intent of this is to actually save the currently selected cell values into a string called str last
00:57value as a variable which is a global variable here as well. Now let's look at the worksheet change
01:02here. So like I said this on this subroutine get only executed when you make changes to your cell values
01:07here. The first section of the code prevents infinite recursive calls because like I said this get executed
01:14when you make changes to the cell. There's a couple of cell changes that we're performing here. This
01:19section of code prevents it to go any further because your exit subroutine and the only time this will
01:26allow you to execute the remaining of the code is when all these are finished and this boolean lock
01:33is set to false. So let's look a bit further here. This section essentially just defines a variable and
01:38associate the worksheet name to a variable or rather the object name called log sheet here. And then in
01:44this section here is where you start writing the log on the log sheet. This one returns the last cell
01:51in the first column. The first column pertains to this number here. Likewise, this one returns the very
01:57last cell on the second column. Very last cell on the third, fourth, and so on columns. And then there's
02:06another section here of this routine. It calls for NXLOOKUP which is essentially an XLVBA method that is used to
02:14find the N of range which is non-empty in a cell in a particular direction. In this case, it's
02:21going to
02:21XL upwards. So if you put these two routines together, these two statements in a nutshell will return the
02:29last row of the log sheet. And if you want to put things into perspective, if I go back to
02:34my VBA here on
02:37the log in particular, it essentially means that it returns the very last entry on your log sheet.
02:43this guy here. And then there's another section right of that is called offset by one. This one will
02:49return the next adjacent value or the next row after the very last row, which allows you to start entering
02:57all the latest log on the bottom of this list here, which you will see here shortly. So we are
03:03tracking
03:03the current date timestamp as now. And then this target cell actually captures the sheet name itself.
03:12So when you make changes to sheet number 330, it will be reflected here. If you make changes to sheet
03:17313, it will be reflected there as well, and so on. And then this address is basically the cell location.
03:26And this STR last value, it's got captured here. When you make changes to a selection
03:33on your cell like that, it's basically saving, let's say Isabel here into a variable and essentially
03:39populating back if you make any changes. And this target value is from this argument here,
03:45is whatever new values that you have entered. So basically, if you look at your log file, you'll
03:50capture the dates timestamp. It will do a sheet name, where the changes was made, what was the changes
03:57before, and what's the new changes here. Let's go ahead and go to our VBA, CTRL-S to save, and
04:03let's
04:04close our VBA editor here. These two are the same worksheet, it's just that I put them in the two
04:09different windows. So you can actually see them in action. You can see it's the same log sheet that I'm
04:14looking at. So let's say for example, if I were to change McKenzie to say Neo, if you hit enter
04:21here,
04:22you can see that the last known value was McKenzie, and Neo is a new change. So basically, there's two
04:29subroutines running to capture and to update the new value here. And likewise, let's say for example,
04:35if you were to upgrade this Charlie's Robinson to account manager, and you type account manager like
04:44you can see that it captures the before and after. You can also make changes to the employee ID.
04:51You can see that what's the last known value, and what's the new change.
04:59And just as a bonus, if you're wondering how I format this timestamp and the date, if I go say
05:06CTRL-1 to open up cell formatting for this particular cell, you can see this is the formatting
05:12structure I'm using it.
Comments