 In this video, we're going to be creating a change log that allows you to store the user name every time someone saves this file. It's particularly beneficial if like you've got multiple people working on this file and you want to create a log that allows you to see every user who has saved this file. Particularly useful if you notice that the last user had made a change or something's been deleted. You want to know who to speak to to try and resolve that issue. I did touch on this potential solution in a previous video and you will notice that this video is going to utilize some of the script that we've looked at in the last video and previous videos and basically bringing those together now to actually build a real working solution. I hope to bring you benefit in your Excel work. So if you haven't already, please do subscribe to the channel. Make sure you hit that bell notification button so you're notified of our future videos. And if you do enjoy this video, please make sure they hit that like button. So we'll jump straight into it. You can see on the screen we've got like two sheets. The first one is a data sheet and just some basic data. I don't know why I really even put that in there, but it just serves the purpose of showing you, you know, you got some data here. What's really going to be updated with dates and people's names. And we want to create a change log that you'll see on the second sheet here where we can store the username every time the file is saved. So to code this, we need to open up our macro window. And once we're in there, we need to make sure we're in the this workbook tab on the left here. And we can just jump straight in. And to make this work, we're going to be using the change event of before save. So I believe it was the last video where we looked at before save. So we're going to be using exactly that same change event. It's just that we're going to be adding some extra lines of code to enable us to do this storing of the username as required. So we'll type it straight away. So we need private sub and we're going to do workbook underscore before check or workbook underscore before save. Open brackets, sorry, by val save as ui as boolean. And then we're going to go cancel as boolean. Close brackets, hit enter and you can see it's done the formatting for us and we've got that end sub put in there as well. So the first part we're going to need to do here is we need to define a couple of variables. So the first one is this worksheet. So we need to define this change log sheet. So to do that, we're going to go dim wb and recall that the log as worksheet. And the next thing we're going to need to do is when we're storing our username, obviously we don't want to overwrite. So far as to put my name in here is the username. For the last person who saved this file, we need to make sure that our code is going to put the next username in row three. And obviously if I put the name in row three, we then need to make sure the next one does it in row four. So we need it to dynamically update and obviously put it in the next empty row. And you will have seen this code we're going to be using in our previous video as I mentioned at the start when we did the copy and paste data. And if you can't remember, we'll cover it off now. But if you want any more detail by all means, go check that video out now or before at the end of this video just to familiarize yourself. So I'm going to say dim and I'm going to call this I last row. And we're going to find this as an integer because it's a whole number. And then we can come down here. And so that's defined our first or only two variables there. The first thing we needed to do now is obviously set what this worksheet is. So WB log what does that refer to? So I'm going to set WB log and that equals sheets change log. So simple as that. It just obviously refers to that sheet. And the next part is I last row. So what does this value equal? So for us, it's going to be obviously we want to look in this log sheet into column A and find out the last row or the next empty row. So we need to first refer to that sheet. So what WB log dot cells open brackets. And then again, we now need to reference that again. So what WB log dot rows dot count. So we need the count number of rows being used in column A and we can close our brackets. The next part is ends because we want to go to the end of that row and go Excel up dot row. So that first part here is going to literally go the same as if we're going to go into here and do control down. It's literally going to go to that last row there. But obviously we don't want to go just to the last row has got some information in it. We want to go to the next empty row. So we want our code to first say, okay, go to the last row has got information in it and then add an additional one just so we know you're in the next empty row. To do that, all we simply need to do is go plus one at the end here. This first part here is just going to return an integer for us. So it's obviously going to tell us at the moment number three. So we just want to add one to that to give us the value of four. And this is simply as that input is all that output is as simple as we need it. So once we've then done that, we can now actually move on to actually doing something with this data. So the first thing you want to do, well, the only thing we want to do, shall I say, is put the username in this next empty row. So for us to do that, we just need to go WB log dot cells, open bracket. And then the first thing we need to do is reference the row number of the cell in question. So for us, that's literally going to just be this I last row. So all we need to do here is type in I last row, what is going to be able to see an integer value. So it'll give us the number in this sense, it'll give us number four. And we want it to be in column number one. So column index number one is all we need to do there. Dot value equals environment, open brackets, username, close. And that is it. So that is all the code we need to do to create this log to store the username every time this workbook is saved. And if I just now clear these two values here, so we start from refresh and now click the save button. You can see that every time I hit save, the username obviously it's the same because it's just going to be me is added into that list. So as we said, the real benefit of this is you could hide this sheet and I'll go into that in a minute. So it's in the background. And obviously as you keep saving this, then obviously it'll give you a log of all the usernames who will save this workbook. So that's kind of part one completed. But actually we want a bit more detail than this. It's great to see that these users have updated the workbook, but we don't know when they updated the workbook. So what we're going to do here is just add another column called date. And let's just copy that formatting as well. And we'll just clear these values here. So what we're going to do is we're going to literally just copy this. Well, actually it's not copy. Let's type out again. Just get keep familiarize and how this is written. So what we want to do we run this code is we want to obviously put the username here in column number one. But we actually want to put the date now in column number two. So to do that we just go WB log because obviously we're referring to the same sheet dot cells open brackets. So it's going to be the same row. So I last row comma, but this time we want to refer to column number two. Close brackets dot value of this one is simply going to equal now. And now is a built in function to us. And this literally just going to enter in here the current date and time. So basically the current date and time as this piece of code is executed. So we don't need to worry about obviously doing another looking for the new row rate value here because it's going to be exactly the same as the last one. And obviously we want to make sure it is the same because we don't want them to be squiffed on different rows. So this time if I now go to save the workbook. Oh, we've got an error. There we go. I didn't type that correctly. I was trying to go too quickly obviously. So let's go into cells and then stop that. So if we now go and run this work or go to say this workbook, you can see how we now have the username and the day also available to us in column B. And by default, you can see I've got the time there. But if you wanted to just have a date, all you simply need to do is just go right click or go into your dropdown at the top there, format cells. And then you can select either the time date or whatever format preference that you have for me. I've literally left that as it is. So we could just go format cells and we'll do a custom one or go time note, custom one maybe. And let's try and find, you go date, hours, minutes. And we'll just add seconds onto there as well. Cool. So now every time that this gets up, this gets saved. Sorry. You can see that it's going to capture the username, the date. And obviously I'll just put the seconds on there as well just so you can see obviously it's a different value that every time it's being run. So that is our user log. And we can actually utilize one other feature we looked at in the previous video just to tidy this code up ever so slightly. So you're never in our previous video, we looked at the with function in our script. So that basically allows us just to sort of tidy up the code we have. If we add with in here, we can literally get rid of the WB log text here. And I'll just put with below so we can see the difference. So we're going to do with and WB log because obviously we're referring to that same sheet to update these values. And we just need to do any with here as well. If we tab in, so rather than having to type out WB log each time, we can just tidy up with this function here. So we've got dot cells and we go I last row actually rather than type this out. I can actually copy it be a lot easier and more not as boring to watch. So we can copy that one there and paste it. And then we can also copy this one here tab in dot that get rid of this. And you can see we're already looking quite a bit tidier. So now when I save the workbook, you can see it continues update as required. So there you have it. We're just adding a simple couple of rows. We've now made our change log using that same change event of before save. But obviously by using the actual dynamic aspect to be able to find the next available row, we're able to create this log as required. So I hope you enjoyed that video. If you did, please do hit that like button. It's not only greatly appreciated by me, but obviously helps that all important YouTube algorithm. And if it's the first time watching our videos or you have watched them before and you're still not yet subscribed, please do subscribe to the channel, hit that bell notification button. And that way you will be notified of all of our future videos as they come out. So thank you very much again for watching and I'll see you in the next video.