View original (French)

[VBA] date format in textbox

antic80 Posted messages 4877 Status Contributor -  
 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

13 answers

  1. horseloverfat
     
    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).
    17
    1. Syssy
       
      It's simple and effective. Thank you.
      0
    2. nabilone
       
      Thank you.
      0