MrExcel Publishing
Your One Stop for Excel Tips & Solutions

date problem

Posted by lucky on January 21, 2002 7:15 AM

I have a Userform that AutoOpen's when the file opens that has a number of text boxes that require input by the user. One of the text boxes requires a date input in the format dd/mm/yy and has the contol source set at Sheet1!A1. I have cell A1 date formated dd/mm/yy, but when I input a date (e.g 01/04/02) into the text box, the cell A1 returns the date in mm/dd/yy format (e.g 04/01/02)
Is there any way I can format the textbox maybe? Or any other ideas??
Thanks again.

Posted by Damon Ostrander on January 21, 2002 12:48 PM

Hi Lucky,

The problem here has nothing to do with the Textbox. You will notice that when you type 01/04/02 directly in the cell that is formatted dd/mm/yy that it will interpret it as Jan 4, 2002. Just because it displays it in dd/mm/yy format doesn't mean you can enter it that way. Interesting.

To get around this you could write your own text parsing code for the textbox to reformat the text, reversing the day and month, before entering it in the cell. This is not hard, but a bit tedious. Probably a better way is to simply use three textboxes for the date entry, one each for day, month and year. Suppose then that you name the three textboxes DayTB, MonthTB, and YearTB. You would then enter the date into, for example, cell B4 on the active worksheet:

[B4] = MonthTB.Text & "/" & DayTB.Text & "/" & YearTB.Text

which does the order reversal that is needed.

Let us know when you win the lottery.


Posted by lucky on January 22, 2002 2:52 AM

Thanks for the idea Damon
This weeks lucky numbers
07 08 19 31 33 34
Good luck