Florian wants to time stamp a record every time someone types a new name in column A, but the NOW function is not working. In Episode 680, I will show you a few lines of VBA code to solve this problem.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netast. I'm Bill Jelen.
We have a great question sent in by Florian from Germany.
Florian said. Hey, I'm interested in the NOW function.
Take a look at this NOW function over here.
Basically anytime I enter a name in column A, I want to have a timestamp show up in column B.
So I'm using the NOW function to do that.
He says but the big problem is when I enter the name, because he's declared this as a list in excel 2003 or a table in excel 2007, the formula automatically extends.
That's great. You'll notice right now everything's at 7:32:48 and we get the new time 7:34:05 is when we entered that.
However, it changed all the previous timestamps as well and ofcourse, that's what it's doing because the NOW function, every time that you change the worksheet, the entire worksheet recalculates and all of the times change.
He says. This is not what I want.
I want to keep that date and time.
Well, we can do this with just a few lines of VBA code.
I'm going to switch to another worksheet here "Name" "Date/Time" and I'm going to switch over to the VBA editor This worksheet is called "New" and I don't want to enter the code on a module.
I'm not going to create any modules or anything. I'm actually going to go right to the worksheet.
So click on "New", actually double click there.
From the left drop-down we're going to choose worksheet and from the right drop down, we're going to choose "Change".
This creates a tiny little macro. That'll run every time we change the worksheet and it gives us a variable called Target.
Target is a range so it has all the properties of any cell.
Basically, we're going to write a line of code here that says.
If Target.Column=1 then in other words if they just entered some data in column A.
What do we want to do, we want to write the current date and time over to column B.
So we'll say Cells (Target. Row, 2). value = Date + Time.
Now these are variables that are provided by VBA, date basically gives us today, time gives us the current time, we have to add them together.
So it's a little bit different than using NOW.
All right now, you would think that we would basically be done here.
But there's a problem that this bit of code gets run every time the worksheet changes and the code potentially is going to change the worksheet.
So the code is going to end up being called recursively.
Now in this case it probably won't be a problem, but I'm going to add in Application.EnableEvents = False.
right before I write to the worksheet and then Turn that back to "True", right after it write to the worksheet.
This will prevent the subroutine from getting called multiple times. Now let's go back and test it.
We'll go back to our worksheet here I'll type in Bill. You'll see that it was 7:36:17.
Wait a few seconds, type in the next name, the original one stays at 7:36:17.
The new one though has the time of 7:36:23.
So that's called an event handler.
Tiny little bit of code not much code at all to make that work very simple and it'll solve the problem that Florian sent in..
Thanks to you for stopping by. Will see you next time for another netcast from MrExcel
We have a great question sent in by Florian from Germany.
Florian said. Hey, I'm interested in the NOW function.
Take a look at this NOW function over here.
Basically anytime I enter a name in column A, I want to have a timestamp show up in column B.
So I'm using the NOW function to do that.
He says but the big problem is when I enter the name, because he's declared this as a list in excel 2003 or a table in excel 2007, the formula automatically extends.
That's great. You'll notice right now everything's at 7:32:48 and we get the new time 7:34:05 is when we entered that.
However, it changed all the previous timestamps as well and ofcourse, that's what it's doing because the NOW function, every time that you change the worksheet, the entire worksheet recalculates and all of the times change.
He says. This is not what I want.
I want to keep that date and time.
Well, we can do this with just a few lines of VBA code.
I'm going to switch to another worksheet here "Name" "Date/Time" and I'm going to switch over to the VBA editor This worksheet is called "New" and I don't want to enter the code on a module.
I'm not going to create any modules or anything. I'm actually going to go right to the worksheet.
So click on "New", actually double click there.
From the left drop-down we're going to choose worksheet and from the right drop down, we're going to choose "Change".
This creates a tiny little macro. That'll run every time we change the worksheet and it gives us a variable called Target.
Target is a range so it has all the properties of any cell.
Basically, we're going to write a line of code here that says.
If Target.Column=1 then in other words if they just entered some data in column A.
What do we want to do, we want to write the current date and time over to column B.
So we'll say Cells (Target. Row, 2). value = Date + Time.
Now these are variables that are provided by VBA, date basically gives us today, time gives us the current time, we have to add them together.
So it's a little bit different than using NOW.
All right now, you would think that we would basically be done here.
But there's a problem that this bit of code gets run every time the worksheet changes and the code potentially is going to change the worksheet.
So the code is going to end up being called recursively.
Now in this case it probably won't be a problem, but I'm going to add in Application.EnableEvents = False.
right before I write to the worksheet and then Turn that back to "True", right after it write to the worksheet.
This will prevent the subroutine from getting called multiple times. Now let's go back and test it.
We'll go back to our worksheet here I'll type in Bill. You'll see that it was 7:36:17.
Wait a few seconds, type in the next name, the original one stays at 7:36:17.
The new one though has the time of 7:36:23.
So that's called an event handler.
Tiny little bit of code not much code at all to make that work very simple and it'll solve the problem that Florian sent in..
Thanks to you for stopping by. Will see you next time for another netcast from MrExcel
