Date Format when writing back to Spreadsheet

I though I had solved this problem but I have not.

I have a spreadsheet and a user form, I input data through the user form and it writes the dats to the spreadsheet, my problem is that everything is set to UK format dd/mm/yyy but when the form writes the data back to the spreadsheet it displays it on the spreadsheet as US formatt mm/dd/yyy even throguh it is formatted as UK. Please can someone help me, I have now idea what to do.

A copy of the database is attached.

Shazz.
xx

We seem to be doing all the work here.

I answered a question last night about the form overwriting formulas which added the dates.

Are you using these formulas or using dates input with the form?

Adapt this code

Please Login or Register  to view this content.

Use DateSerial & NumberFormat as I have shown in my code, in my opinion it's the most reliable method.

What error is reported, have you a date in the TextBox?

Dates are so temperamental I don't take any risks.

FWIW I used to use DateSerial but changed to DateValue a while back and haven't run into any problems that I can think of.

Dom

You mean you get no error message at all? If so, what happens if you simply press f5 when the code stops?

Then whatever is in that textbox is not a date.

If it happens as soon as you open the form, why is the form trying to save data?

Post the current file.
I suspect you are setting the row number which is triggering the save automatically, in which case you need an escape variable that allows you to change controls without triggering their events.

Ok, I think I know the problem but I do not know how to fix it, If there is not entry ie date in the date fields it is showing the run time error. the only prolems is not all the date fields will be completed, do you know how I get around this.

Also when entering the date in it is showing as US format on the spreadsheet even though I am setting it to Uk dd/mm/yy. do you have any suggestions??

Shazz

So it wasn't a valid date then!

Add this function to the code

Please Login or Register  to view this content.

and then use

Please Login or Register  to view this content.

and so on.

In the userform, below your existing routines.

It should do.

I have done and now I am getting the below error:

Compile Error:
Ambiguous Date Detected: Getdate

Please Login or Register  to view this content.

See attachment, would you be a complete honey and maybe change what ever I have done wrong, that way I will not have to keep bothering you anymore.

Shazz
xx

You have pasted the function in twice - remove one version.

Note: some of your date columns are formatted to display in US date format, which I suspect is where your confusion lies.

I cannot attach the revised file (due to forum gremlins - i.e. the buffoons who program it) so you will have to make the changes yourself, I am afraid.

I still cannot attach, but in the file you posted a lot of cells were formatted as US date.

Because I simply get an error page every time I attempt it. Not the first time - every time they fix it, they break it again later.

Your file works for me - are you sure your regional settings are not US on your computer?

I don't think so, all my other spreadsheets are fine where the date is concerned, How would I check that?

I really am so sorry to be such a pain, this is the last thing I need to sort out and it really is bugging me.

If I PM'ed you my email would you send it through?

Shazz
xx

Check under Control panel - regional options.

Is it all the dates from the form that convert, or just some?

I still cannot attach the file, so if you PM me an email address, I will send you the revised copy, which works for me.

If you format the cells in the sheet as dd mmm yyyy then you can load them into the form using the Text property of the cell rather than the Value property.

One last thought - in the Regional settings in control panel, what is your Short Date format set to?

Pretty much exactly as I said. Change the format of the cells (select, then Ctrl+1) to dd mmm yyyy and then where your LoadData code refers to .Value use .Text instead.

If anyone else is still following this, I would be intrigued to know if they see the dates in UK or US format.

No - only the date ones are required (unless you want to put formatted values in any other textboxes).

One other long shot. Remove the code from the Exit events of your textboxes that formats the contents as a date. It should not be necessary.

All the date textboxes need to loaded the same way you have done textbox9

Glad we got there in the end!