In honor of Memorial Day in the United States, a question sent in from a naval aviator who has to enter a series of time values. Using a tiny bit of VBA code, we can eliminate the need to type the colon in the middle of the time value. Even if you dont have to enter times all day, the techniques in Episode 516 can ease the redundant data entry tasks.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey! welcome back to the MrExcel Podcast.
I'm Bill Jelen.
Well today is Friday, before Memorial day weekend, in the United States.
This is a three-day weekend for us kind of first kickoff for the summer chance to thank all the veterans.
I want to send a shout out to my dad, Bob Jelen.
Who's celebrating his 85th birthday this weekend, veteran from World War II.
Today, we have a podcast...
It has a great military use.
This question was actually sent by someone, who's working recording times in the military, and he said hey "I have to always go through and record times that certain events happen." I think it was planes landing, on an aircraft carrier and he said it is so annoying for me to have to type the colon for example, if a plane lands at 12:34, I could use the numeric keypad on the desktop computer, today.
Enter the 1 and 2 but then I have to go over hit the [ shift ] key hit the [ colon ] and enter the 3 4.
Is there some way to solve that problem?
And you will be amazed at what is actually available with a tiny bit of VBA code.
It turns out that Excel has the hook in it.
So, that way it can run a few lines of VBA code, every time you make an entry in a cell.
Now, here's how we get there. We're going to hit [ Alt F11 ], which will get us back to the VBA editor and in the project window, if you don't see the project window, you go to view, project explorer and the project window look for your workbook.
You might need to expand it and then look for Microsoft Excel objects.
You might need to expand that and then finally double click on the sheet name, where you want this trick to happen.
So, we double click on that sheet.
There's two drop downs at the top from the left drop-down, choose worksheet and from the right drop down.
These are all the events that we can capture.
We choose, the change event.
So, we have the skeleton of a tiny little bit of VBA code here for worksheet change.
Now, I'm going to paste in some code, will talk about the code real briefly.
Basically, the macro is giving us a variable called target and that target is the range, that was just entered.
So, I can say, hey! If the column number of that target is column C.
Let's say in this case, they would always enter the times in column C.
Then we're going to take the value that I typed in take the leftmost two characters, concatenate it with a colon and then concatenate it with the right two characters.
Now, the very first time we wrote this.
We didn't realize exactly what was happening because when we wrote that value back to the original cell that actually is a worksheet change.
So, Excel would say oh hey! They just changed the worksheet.
Let's go run this code again, and it was just creating chaos until basically we ran out of memory.
So, the one thing you have to do to make this work is a couple of lines of code, where we say we're going to take application dot enable events.
This is a property and turn it off.
We're not going to allow any event handlers, to run.
We write the value back to the spreadsheet and then turn the event handler back on.
So, now let me go back and show you exactly how this works.
We're in column C.
We're going our value of 1 2 3 4 and watch when I hit [ enter ].
There'll be a brief pause, but then all of a sudden the colon will show up and we actually have a real time.
If I go back you'll see that Excel converted it to a real time.
It also works with military times.
So, if something happened at 23:45.
It adds the colons in and converts it to the real time.
This is a great trick.
Now, if you don't have to enter times, but you have some huge annoying situation, where you have to enter a few digits and then a weird character and then a few more digits.
A little bit of VBA code will go along way.
Now, if you've never used VBA before I want to point you to MrExcel message board.
Over 30,000 questions a year or answered, and we love to answer VBAquestions.
Just say hey, I'm completely new to VBA.
I need to figure out how to do this event handler and people will be happy, to troubleshoot your code and get you going.
Hey! Thanks for stopping by, if you're in the United States, please have a great and safe three day weekend.
Otherwise, we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Well today is Friday, before Memorial day weekend, in the United States.
This is a three-day weekend for us kind of first kickoff for the summer chance to thank all the veterans.
I want to send a shout out to my dad, Bob Jelen.
Who's celebrating his 85th birthday this weekend, veteran from World War II.
Today, we have a podcast...
It has a great military use.
This question was actually sent by someone, who's working recording times in the military, and he said hey "I have to always go through and record times that certain events happen." I think it was planes landing, on an aircraft carrier and he said it is so annoying for me to have to type the colon for example, if a plane lands at 12:34, I could use the numeric keypad on the desktop computer, today.
Enter the 1 and 2 but then I have to go over hit the [ shift ] key hit the [ colon ] and enter the 3 4.
Is there some way to solve that problem?
And you will be amazed at what is actually available with a tiny bit of VBA code.
It turns out that Excel has the hook in it.
So, that way it can run a few lines of VBA code, every time you make an entry in a cell.
Now, here's how we get there. We're going to hit [ Alt F11 ], which will get us back to the VBA editor and in the project window, if you don't see the project window, you go to view, project explorer and the project window look for your workbook.
You might need to expand it and then look for Microsoft Excel objects.
You might need to expand that and then finally double click on the sheet name, where you want this trick to happen.
So, we double click on that sheet.
There's two drop downs at the top from the left drop-down, choose worksheet and from the right drop down.
These are all the events that we can capture.
We choose, the change event.
So, we have the skeleton of a tiny little bit of VBA code here for worksheet change.
Now, I'm going to paste in some code, will talk about the code real briefly.
Basically, the macro is giving us a variable called target and that target is the range, that was just entered.
So, I can say, hey! If the column number of that target is column C.
Let's say in this case, they would always enter the times in column C.
Then we're going to take the value that I typed in take the leftmost two characters, concatenate it with a colon and then concatenate it with the right two characters.
Now, the very first time we wrote this.
We didn't realize exactly what was happening because when we wrote that value back to the original cell that actually is a worksheet change.
So, Excel would say oh hey! They just changed the worksheet.
Let's go run this code again, and it was just creating chaos until basically we ran out of memory.
So, the one thing you have to do to make this work is a couple of lines of code, where we say we're going to take application dot enable events.
This is a property and turn it off.
We're not going to allow any event handlers, to run.
We write the value back to the spreadsheet and then turn the event handler back on.
So, now let me go back and show you exactly how this works.
We're in column C.
We're going our value of 1 2 3 4 and watch when I hit [ enter ].
There'll be a brief pause, but then all of a sudden the colon will show up and we actually have a real time.
If I go back you'll see that Excel converted it to a real time.
It also works with military times.
So, if something happened at 23:45.
It adds the colons in and converts it to the real time.
This is a great trick.
Now, if you don't have to enter times, but you have some huge annoying situation, where you have to enter a few digits and then a weird character and then a few more digits.
A little bit of VBA code will go along way.
Now, if you've never used VBA before I want to point you to MrExcel message board.
Over 30,000 questions a year or answered, and we love to answer VBAquestions.
Just say hey, I'm completely new to VBA.
I need to figure out how to do this event handler and people will be happy, to troubleshoot your code and get you going.
Hey! Thanks for stopping by, if you're in the United States, please have a great and safe three day weekend.
Otherwise, we'll see you next time for another netcast from MrExcel.
