Date formatting...

amy

I have written a small peice of code for transferring a date to a spreadsheet from a userform - as below:

Sheets("SEPT03").Range("A7") = TextBox1.Text
Selection.NumberFormat = "dd/mm/yyyy"

(userform requires you to enter the date in the specified format)

I am currently one step short of putting my fist straight through the screen of my computer, because the date transfer insists on being in american date format up to the 12th of every month, and then in english format (the way I want it) for dates after 12th...I have tried everything, I've changed regional settings, I've changed the settings in cell formatting in excel...and I can't work out why it's doing it...

Can someone tell what the hell I am doing wrong???!?!?!!

Don't mean to sound too p***ed off, but this is driving me nuts...

Thanks in advance

Tim

Did you try using the Format VBA fucntion before you paste, i.e.

Sheets("SEPT03").Range("A7") = Format(TextBox1.Text,"dd/mm/yyyy")
Selection.NumberFormat = "dd/mm/yyyy"

it still seems to be doing the same thing...I've just hit a brick wall with it...

any other ideas?

With UK dates, VBA is notorious for swapping the day and month when they are ambiguous. This worked for me:Code:
Private Sub CommandButton1_Click()   With Sheets("SEPT03").Range("A7")       .Value = DateValue(TextBox1.Text)       .NumberFormat = "dd/mm/yyyy"   End With
End SubYeah, that is a known "bug" with that affects English versions.  Here are a few threads and how they got around it:

board2/viewto...glish+date+bug

board2/viewto...glish+date+bug

Thanks for the code, It was a very nice little code, but it didn't work.

I'm afraid I shall be putting my fist through the screen now...

Sometimes, I really wish that Bill Gates had been born an Englishman...

Thanks for your help anyway.Did you check out the threads I posted?

As I say it worked for me. If I entered 12/10/2003 in the text box I got 12/10/2003 (12th October serial number 37906) in cell A7. What do you get?

FAO: Andrew

I get 37934 as a reference number...

FAO: Jmiskey

Yeah, checked out the threads, can't seem to find the right bits to make it work...

What are your Regional Settings (now that you have tinkered with them)? Mine are English (United Kingdom) with short date dd/MM/yyyy.