Issue with Userform READING dates as USA

[AnvarJay]

Hi everyone, I have searched for help in many forums etc and all look and sound right but none are working for me.

As you can see my Spreadsheet is in dd/mm/yy but as my userform reads the data it changed it to mm/dd/yy and i set a msgbox to show also incase the value of the item changed again. I can get it to right the data as dd/mm/yy but when i then go back in again the spreadsheet is correct at dd/mm/yy but the userform again reads it as mm/dd/yy and i must again manually change it even if i dont need to edit that field.

Thats the code for pulling up the form to show what is already in the sheet, except it chaged to mm/dd/yy. Also my local settings are all as Australian, and set to local - *dd/mm/yy settings.

Does anyone have some suggestions. This is just a play with file to get the dates working as all of the rest of the real sheet is fine and doesnt do anything to the dates anyway. So this sample will be all i need to get right to then put it in my larger file code.
Thanks

 

[Dermot]

That's odd. I'm in Australia, too, and not having that problem.

If I set
TextBox1 = Format(Range("qaz").Value, "dd/mm/yy") where named range "qaz" is a date, the result looks ok
and this line MsgBox "Today is " & TextBox1 gives me the right format

I am using Excel 365. What is your version?

 

[AnvarJay]

That's odd. I'm in Australia, too, and not having that problem.

If I set
TextBox1 = Format(Range("qaz").Value, "dd/mm/yy") where named range "qaz" is a date, the result looks ok
and this line MsgBox "Today is " & TextBox1 gives me the right format

I am using Excel 365. What is your version?

all this above was done on ver 2010 but at work where i am making a MUCH larger project is 2007 which doesnt have half of the things that can make life easier but getting used to it. I will try the format that way and see if that helps. thanks

 

[dmt32]

Does anyone have some suggestions. This is just a play with file to get the dates working as all of the rest of the real sheet is fine and doesnt do anything to the dates anyway. So this sample will be all i need to get right to then put it in my larger file code.
Thanks

Use the Range.Text property - this returns the formatted text (what you see in the cell) for the specified object

Me.TxtCustomer.Value = ws.Cells(ActiveCell.Row, 1).Text

Dave

 

[AnvarJay]

That's odd. I'm in Australia, too, and not having that problem.

If I set
TextBox1 = Format(Range("qaz").Value, "dd/mm/yy") where named range "qaz" is a date, the result looks ok
and this line MsgBox "Today is " & TextBox1 gives me the right format

I am using Excel 365. What is your version?

OK nearly got it thanks. Now i have these two lines of code below.

'Me.TxtContacted.Value = Format(Range("ws.Cells(ActiveCell.Row, 2)").Value, "dd/mm/yy")
Me.TxtContacted.Value = Format(Range("B2").Value, "dd/mm/yy")

I have commented 1st line out as it comes up with this eeror, which in pict also shows lines of code.

but the line under using range("B2") works and cell B2 is correct but i need the code to auto pull up the Activerow as it changes depending on wwhat line i am in the spreadsheet and needs this code to autochange with it.
Any thoughts?

 

[AnvarJay]

Use the Range.Text property - this returns the formatted text (what you see in the cell) for the specified object

Me.TxtCustomer.Value = ws.Cells(ActiveCell.Row, 1).Text

Dave

i the 2 diff workbooks i am playing with all this, the text columns like the one u show all r listed as .Value not as .Text and are fine, it is only dates I am having an issue with.

 

[Dermot]

The error can be fixed like this - get rid of the Range part
Me.txtContacted.Value = Format(ws.Cells(ActiveCell.Row, 2).Value, "dd/mm/yy")

 

[dmt32]

i the 2 diff workbooks i am playing with all this, the text columns like the one u show all r listed as .Value not as .Text and are fine, it is only dates I am having an issue with.

Code was just an example - You can just apply the Range.Text property to the range(s) your code reads the dates from & this should solve your issue.

Dave

 

[AnvarJay]

t

The error can be fixed like this - get rid of the Range part
Me.txtContacted.Value = Format(ws.Cells(ActiveCell.Row, 2).Value, "dd/mm/yy")

hat worked perfectly thanks.

Now trying all of the other and same combos i cant get it to write the date now back to the sheet in Aust date format.

Here is a really weird part -

The code above where the format is on the last part of the line, seemed to swap it from which ever it is to the opposite between usa and uk formats. For ex, i didnt change it back in sheet to Aust/UK format then ran the userform again and clicked update (so currently it was reading TxtAccepted as AUST but with the lowest line u see, swapped it back to usa first time, then when i ran it again and didnt change it back it then rewrote it as Aus. So which ever i have there it just reverses it, but if i dont format it at all it always puts it as USA.

Which it shows on the Format line for the With ws code part. I remember so much now why i decided i didnt want to be a programmer 20 something years ago, too many stooooopid little issues - lol

 

[Dermot]

Instead of using the Value of the textbox, have you tried using Text?
= Format(Me.TxtAccepted.Text,"dd/mm/yy")