Force Date Format In TextBox

  • Hi everybody


    Obviously there are hundreds of threads on the above subject but really i have not been able to find the correct answer and I myself have not been able to figure out the same...so thats why I am asking all you excel gurus to pl help me out


    On a user form i have a textbox for accepting date. Earlier I had linked it to calendar but now the user does not want calendar but a direct input in the textbox whereupon clicking a commandbutton the date will get supplied to another code.


    My problem is this


    1...How do I make the textbox accept date only in the "dd/mm/yyyy" format
    2....ensure that error message is displayed if there is departure from format (even though the date may otherwise be valid)
    3....supply the date in "dd/mm/yyyy" format to the subsequent code


    Thanx in advance


    pangolin

  • Re: Date format in text


    pangolin,


    First off letting the user type in the date string can be problematic. Hence, I believe you're better off using a date control. However, users being users, want want want. So if they want to type in the date on the exit event of the text box use the function ISDATE.



    It'll be on them to enter the MM/DD/YY correctly.

  • Re: Date format in text


    Hi Pangolin,


    The following code will allow the user to put in different formats, but change after input to dd/mm/yyyy. Clicking the CommandButton will put the value of the date TextBox into Cell G1 of Sheet1 in the format dd/mm/yyyy.



    Hope this helps.


    Regards,


    Bill

  • Re: Date format in text


    Hi ranger


    Thanx for ur help...the code works but if I put a date valid in mm/dd but invalid in dd/mm (for eg 18/12) it still accepts the date..in such a case I would like it to give an error message and disallow acceptance


    secondly after acceptance the value in the textbox reverts back to "mm/dd" format...can it stay in the "dd/mm" format


    thanx once again


    pangolin

  • Re: Date format in text


    Hi Pangolin,


    Can you check in the Control Panel, that your date is not set to U.S. Format, as you seem to want European Format in your first post. The date should return to dd/mm/yyyy in the TextBox.


    I will have a look at the invalid dates problem.


    Regards,


    Bill

  • Re: Date format in text


    Hi Ranger


    Yeah you very right the date was in the US format (sorry bout that- didnt check over there in the first place)


    However the problem regarding invalid date acceptance still remains...even I am working on it separately but yet unable to figure it out.....


    Anyways...thanx a million for all your help...without you even this much wouldnt have been possible


    pangolin

  • Re: Date format in text


    Hi everybody


    well surprisingly I am not able to find a solution to my problem...


    can someone help me on this


    TIA


    pangolin

  • Re: Date format in text


    Hi Pangolin,


    Try the following:


    Regards,


    Bill