Superbeast326 at YouTube asks if there is a way to add the current date to the right of any 'Yes' entries in a spreadsheet; if a cell is marked 'Yes', then I want the cell to the right of that cell to show the date. Using a simple 'Event Handler' macro, in Episode #1474, Bill creates a means to answer the question and record the date.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1474: date next to yes.
Today’s question is sent in on YouTube.
Superbeast326 says, “Anytime I enter the word yes, I want the cell to the right of it to show the date that that yes was entered.” As I think about this, he probably has a column, right?
There's probably a column that he's going to be entering that in because I can't imagine he would just want it to work everywhere.
So, I'm going to remember here that we want this to work if the yes is in column D, which is the fourth column.
All right, so I’m going to switch over to VBA with Alt F11, in the project explorer we're going to find the worksheets on-- right here on Sheet1.
Double-click, left drop-down choose worksheet, right drop-down we're going to choose every time they change the worksheet.
I'm guessing that he's going to be entering the word yes.
It's not going to be as a result of a formula.
If it was the result of a formula, we would use calculate as we did I think last Thursday.
So, target is a variable, an object variable.
That is the cell that was just changed.
So, if target dot value is equal to 4-- I'm sorry.
If target dot column is equal to 4, then-- in other words, that’s saying if the cell that just changed is in column D, ABCD, then if target dot value is equal to yes-- now, were they entering uppercase, lowercase?
I don’t know.
So, let's convert whatever they entered into lowercase, not in the cell, but just here in VBA.
So, in the spreadsheet, we would use lower, in VBA they use LCase for lower.
All right, if all of that is true, then we want to go to the cell to the right and enter the current date.
We're not going to actually go to that cell.
We're just going to enter the date in that cell to the right.
So, target, that's a variable again, dot offset zero rows down one column over dot value is equal to date.
End if and end if.
This little bit of code is called an event handler macro.
Every time someone changes, the worksheet is going to go do all this.
Now, this is actually going to run a little bit slow because when we change the worksheet to put the date in, it's going to run the code a second time.
So, let's just say right here: application dot enable events is equal to false and then after we enter the date, copy that and paste.
This just prevents the macro from running twice every single time that someone changes a cell.
All right, so we come out here to column F, we enter something, nothing happens.
Enter yes in column F, nothing happens.
In column D enter no, nothing happens.
Enter yes in column D and we get the date.
Very, very sweet trick; works with uppercase, works with upper and lower case.
Works with any way you want to enter yes.
That's going to give you that data.
It will work anywhere in column D. You can page down all the way down here and enter yes and there's a date.
Look how fast it runs.
You can’t even see the screen flash.
It's just like magic, just like magic.
So, hey, I want to thank Superbeast for sending that question in and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1474: date next to yes.
Today’s question is sent in on YouTube.
Superbeast326 says, “Anytime I enter the word yes, I want the cell to the right of it to show the date that that yes was entered.” As I think about this, he probably has a column, right?
There's probably a column that he's going to be entering that in because I can't imagine he would just want it to work everywhere.
So, I'm going to remember here that we want this to work if the yes is in column D, which is the fourth column.
All right, so I’m going to switch over to VBA with Alt F11, in the project explorer we're going to find the worksheets on-- right here on Sheet1.
Double-click, left drop-down choose worksheet, right drop-down we're going to choose every time they change the worksheet.
I'm guessing that he's going to be entering the word yes.
It's not going to be as a result of a formula.
If it was the result of a formula, we would use calculate as we did I think last Thursday.
So, target is a variable, an object variable.
That is the cell that was just changed.
So, if target dot value is equal to 4-- I'm sorry.
If target dot column is equal to 4, then-- in other words, that’s saying if the cell that just changed is in column D, ABCD, then if target dot value is equal to yes-- now, were they entering uppercase, lowercase?
I don’t know.
So, let's convert whatever they entered into lowercase, not in the cell, but just here in VBA.
So, in the spreadsheet, we would use lower, in VBA they use LCase for lower.
All right, if all of that is true, then we want to go to the cell to the right and enter the current date.
We're not going to actually go to that cell.
We're just going to enter the date in that cell to the right.
So, target, that's a variable again, dot offset zero rows down one column over dot value is equal to date.
End if and end if.
This little bit of code is called an event handler macro.
Every time someone changes, the worksheet is going to go do all this.
Now, this is actually going to run a little bit slow because when we change the worksheet to put the date in, it's going to run the code a second time.
So, let's just say right here: application dot enable events is equal to false and then after we enter the date, copy that and paste.
This just prevents the macro from running twice every single time that someone changes a cell.
All right, so we come out here to column F, we enter something, nothing happens.
Enter yes in column F, nothing happens.
In column D enter no, nothing happens.
Enter yes in column D and we get the date.
Very, very sweet trick; works with uppercase, works with upper and lower case.
Works with any way you want to enter yes.
That's going to give you that data.
It will work anywhere in column D. You can page down all the way down here and enter yes and there's a date.
Look how fast it runs.
You can’t even see the screen flash.
It's just like magic, just like magic.
So, hey, I want to thank Superbeast for sending that question in and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
