Date formatting between Excel & Forms

[AndySuk]

Hi

There's been a lot of messages about date formatting when using VBA which I've referred to but I still have a problem that I don't understand and am hoping someone maybe able to help and explain.

I have a form which uses a text box to store a date. A date may be written into this text box by a user and the date is then written to the underlying workbook to be stored for future reference.

When the form is loaded the date is loaded back into the text box on the form.

All that works fine but the date is written to Excel in dd/mm/yy format and the text box then loads the date as mm/dd/yy.

to write to the excel sheet;

Please Login or Register  to view this content.

to write back to the text box;

Please Login or Register  to view this content.

I have tried using different date formats but I always seem to get the same problem. I know that the Excel sheet is displaying the correct date. In other words the text box value seems to be written ok to the sheet. It's when the text box is given back the value that the format is changed.

System date is English UK (dd/mm/yy).

Many thanks

[Leith Ross]

Hello AndySuk,

Do you have the TextBox ControlSource property set to a worksheet cell? This would override your format statement.

Sincerely,
Leith Ross

[AndySuk]

Hi

No I don't have the controlsource set to anything; initially or via the code. The form picks up information depending on which item has been selected in a list box so the contents of the text box are written to the appropriate line on the backing sheet.

Thanks for the thought.

A

[Leith Ross]

Hello Andy,

Since you aren't using the control source property and your code is correct, I am stumped as to what would cause the problem. What version of Excel are you using?

Sincerely,
Leith Ross

[AndySuk]

Excel 2003.

Thnx

[Leith Ross]

Hello Andy,

I will look into this a little more and see if I can find something. This is very odd.

Sincerely,
Leith Ross

[mudraker]

Welcome to the wonderful word of Excel & non America style formated dates

I have seen macros working perfectly for months suddenly stop working correctly overnight for this very reason, with no amount of fiddling with various system settings etc resolving the problem

I have found it is best to load the date to a string variable 1st

One of these methods will work

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Leith I have seen this problem since Excel 5 days

[AndySuk]

Many thanks guys. I can see why this one drives people nuts!

Loading the date to the string variable worked. However I was, additionally, being a plonker without realising it; what I hadn't noticed was that the date, prior to Mudraker's solution was actually being written slightly differently depending on how I formatted the output of the text box and there were times when it was being written as a string rather than a date. As I'd used the format(textbox,"dd/mm/yy") method it seemed to be ok but, comparing it to other ouput I realised it was being written as a string and therefore wouldn't convert to a date.

I think that was a long way of saying I screwed up somewhere so I appreciate all the input.

The cure to THAT part of the problem was to use DateValue around the text output.