This is a great relatively unused part of vba that most people don’t use. I can think of some great uses for key strokes, so thank you for that. One additional piece that I think the coder is going to have to solution for is when the date is a single digit day and month. For example, 1/1/2018. This date here would break your code because now the lengths have changed. It’s relatively easy to solution for that, either evaluate the various lengths dynamically or put in a function to force the 0 before evaluating the lengths. In this case 1/1/2018 becomes 01/01/2018, then you evaluate. Just a small issue I could see with this code. Really great stuff here and I always enjoy picking up new tricks from you!
Very, very good points!! You're 100% correct, this method assumes the user is always going to type using MM/DD/YYYY format, so if they wanted January 8, 2019, they would have to be sure to type '01082019' into the textbox. Brilliant observation and thanks so much for your kind words!!
Dan, thanks for your explanation, this video is awesome and even thought I was not an American and my English was a little rusty, I could understand. Did you have earned a new subscriber.
Thank you or this great tutorial , can the slash show before write month , i mean show automatically before user write the second part. thank you so much.
Jaume Fornos Pinos Yep, there's lots of validation one could use. I would start by using the Mid() function in order to grab the first 2, middle 2 and last 4 during keystrokes.
I’d like to know why when you input three characters into the Me.TextBox1, the IF returns true and executes the “/“ after your third char input when you input “=2” as the condition. It seems the criteria should be “>=2”. Are IF statements just different for Textboxes? Thank you.
WOW! Excellent question. So here's the scoop - when a KeyDown event like this runs, it does not include the actual key pressed in the code. What I mean by that is it's as if the key has not officially been pressed and inserted into the textbox or control until the code has run to completion. Then after you've analyzed or done whatever you want to enhance or even block certain keystrokes (KeyCode = False), then the key may be allowed to run (if you choose). It's kind of like a security guard choosing whether to allow someone into a club. You choose whether the keystroke gets to come through or not. So if you have 2 chars in the textbox and you type a 3rd, the Len() function will only analyze the current length prior to this keystroke coming through. I may have beaten that point to death, sorry if so. Awesome question, rockguitarist!! Thanks Dan
I'm creating a shelf location form for merchandise but when I search for the item if the # of the item starts with a 0, the # will come up without the 0. my question is: is there's a way that if you have a leading 0 in a cell, you can have it display in the textbox as 012345 not 12345
Hi jimmy, Excellent question! It should work if you use the cell's .TEXT property instead of .VALUE, which is the default and probably why it's not been working. me.Textbox1 = Range("a1").TEXT Thanks Dan
This is a great relatively unused part of vba that most people don’t use. I can think of some great uses for key strokes, so thank you for that. One additional piece that I think the coder is going to have to solution for is when the date is a single digit day and month. For example, 1/1/2018. This date here would break your code because now the lengths have changed. It’s relatively easy to solution for that, either evaluate the various lengths dynamically or put in a function to force the 0 before evaluating the lengths. In this case 1/1/2018 becomes 01/01/2018, then you evaluate. Just a small issue I could see with this code. Really great stuff here and I always enjoy picking up new tricks from you!
Very, very good points!! You're 100% correct, this method assumes the user is always going to type using MM/DD/YYYY format, so if they wanted January 8, 2019, they would have to be sure to type '01082019' into the textbox. Brilliant observation and thanks so much for your kind words!!
how do i put this on my work sheet it works but how to activate so all dates typed in a coloum go like this
Thanks for the awesome video!!!!
ExcelIsFun thank you, ExcelIsFun!
Dan, thanks for your explanation, this video is awesome and even thought I was not an American and my English was a little rusty, I could understand.
Did you have earned a new subscriber.
Glad to have to, Valderei! Thanks for the comments!!
Thanks you so much, Awesome video, this will help me lot .....
Jagan S So glad it helped!!
can i do this in a cell instead of a UI
Great video Daniel.
Kevin, you are always so encouraging! Thanks, buddy!
Very nice!!!
Thanks, Rajesh!
Thank you or this great tutorial , can the slash show before write month , i mean show automatically before user write the second part. thank you so much.
Great job , As it could be forced to that the form accepts only digits valid for the month 1 to 12 and from 1 to 31 for the days?
Jaume Fornos Pinos Yep, there's lots of validation one could use. I would start by using the Mid() function in order to grab the first 2, middle 2 and last 4 during keystrokes.
I'm go to try it, thanks.
Thanks for this video upload and I really enjoy going through your Excel VBA course on Udemy, there's a lot to learn there. :)
Thanks S Gain! That's really wonderful. Glad to have you!
how do i put this on my work sheet it works but how to activate
I’d like to know why when you input three characters into the Me.TextBox1, the IF returns true and executes the “/“ after your third char input when you input “=2” as the condition. It seems the criteria should be “>=2”. Are IF statements just different for Textboxes? Thank you.
WOW! Excellent question. So here's the scoop - when a KeyDown event like this runs, it does not include the actual key pressed in the code. What I mean by that is it's as if the key has not officially been pressed and inserted into the textbox or control until the code has run to completion. Then after you've analyzed or done whatever you want to enhance or even block certain keystrokes (KeyCode = False), then the key may be allowed to run (if you choose). It's kind of like a security guard choosing whether to allow someone into a club. You choose whether the keystroke gets to come through or not. So if you have 2 chars in the textbox and you type a 3rd, the Len() function will only analyze the current length prior to this keystroke coming through. I may have beaten that point to death, sorry if so.
Awesome question, rockguitarist!!
Thanks
Dan
I'm creating a shelf location form for merchandise but when I search for the item if the # of the item starts with a 0, the # will come up without the 0.
my question is: is there's a way that if you have a leading 0 in a cell, you can have it display in the textbox as 012345 not 12345
Hi jimmy,
Excellent question! It should work if you use the cell's .TEXT property instead of .VALUE, which is the default and probably why it's not been working.
me.Textbox1 = Range("a1").TEXT
Thanks
Dan
Simple thing described in pathetic way
Cool! Thanks for the special comment!
Very nice!!!!
Thanks, Emil!