[VBA] date format in textbox
antic80
Posted messages
4877
Status
Contributeur
-
nabilone -
nabilone -
bonjour
here is how to force a user in VBA under Excel to enter a date in the format ddmmyy because if, for example, I write 222 in the textbox, it converts it
I want the user to be required to enter, for example, 151205 which will be converted to 15/12/2005
currently I have this for formatting the date to 15/12/2005
TXT_du = Left(TXT_du, 2) & "/" & Mid(TXT_du, 3, 2) & "/" & Right(TXT_du, 2)
thank you
here is how to force a user in VBA under Excel to enter a date in the format ddmmyy because if, for example, I write 222 in the textbox, it converts it
I want the user to be required to enter, for example, 151205 which will be converted to 15/12/2005
currently I have this for formatting the date to 15/12/2005
TXT_du = Left(TXT_du, 2) & "/" & Mid(TXT_du, 3, 2) & "/" & Right(TXT_du, 2)
thank you
13 réponses
Hi.
5 years later and for anyone passing by:
1 - In the Sub TextBoxToto_KeyPress of your TextBox, we only accept numbers
Something like this:
If KeyAscii < 48 Or KeyAscii > 57 then KeyAscii = 0
2 - In the Sub TextBoxToto_BeforeUpdate we check that the entered value is indeed a date. If it is not, we cancel the modification (the old value is restored) and if it is, we set the entered value to the expected Date format.
Something like:
If IsDate(TextBoxToto.Text) then
TextBoxToto.Text = Format (TextBoxToto.Text,"dd/mm/yyyy")
Else
Cancel = True
End If
With this code, the user can enter, for example, 2/2 and it will give 02/02 of the current year in the format 02/02/2011.
Therefore, extracting the month cannot give an error once the modification has been accepted. Moreover, you can add any other validation on your date (< today's date, etc...) in the first branch of the If, since you know you are working with a valid date.
I hope this will help someone passing by (and that since 2006 you've solved your problem :o).
5 years later and for anyone passing by:
1 - In the Sub TextBoxToto_KeyPress of your TextBox, we only accept numbers
Something like this:
If KeyAscii < 48 Or KeyAscii > 57 then KeyAscii = 0
2 - In the Sub TextBoxToto_BeforeUpdate we check that the entered value is indeed a date. If it is not, we cancel the modification (the old value is restored) and if it is, we set the entered value to the expected Date format.
Something like:
If IsDate(TextBoxToto.Text) then
TextBoxToto.Text = Format (TextBoxToto.Text,"dd/mm/yyyy")
Else
Cancel = True
End If
With this code, the user can enter, for example, 2/2 and it will give 02/02 of the current year in the format 02/02/2011.
Therefore, extracting the month cannot give an error once the modification has been accepted. Moreover, you can add any other validation on your date (< today's date, etc...) in the first branch of the If, since you know you are working with a valid date.
I hope this will help someone passing by (and that since 2006 you've solved your problem :o).
Syssy
It's simple and effective. Thank you.
nabilone
Thank you.
