Date changes from UK to US format when stored in cell 1

[Chats]

Hi,

I have a simple userform with one textbox -

InDate

With the following code

Private Sub InDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        Worksheets("Sheet1").Range("A1") = InDate.Value
End Sub

If I enter 1/5/05 (UK format so 1 May 2005) into the InDate textbox, cell A1 on my spreadsheet shows the value "05/01/05" - 5 January 2005)

I have my Windows settings on UK format, the cell is formatted as "dd/mm/yyyy"

Can anybody help - I have tried all ways to fix this and nothing seems to work. This is needed for a larger VBA project I am working on.

I am running Windows 2000, Office 2000, I have tried this on two PCs with the same problem on each.

Thanks for any help...

Ade

 

[TonyJollans]

I answered something on this recently but can't find it at the moment.

The problem is that, when you have a textbox on a userform linked to a cell formatted as a date, Excel interprets the input to the textbox according to US date rules regardless of your regional settings.

Actually it is worse than that. Even if you enter an unambiguous date (say 1-May-2005), it is treated as if it were converted to an ambiguous form and then interpreted as per US rules (so you get 5-Jan-2005). All of this interpretation happens before you have a chance of trapping it (before the Before_Update event).

I'm not saying it's not possible, but I have never found a way to make this work. I have always had to unlink the textbox from the cell and handle it manually.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[littlewoman]

I also encoutered that problem a while ago
To me it was solved when you place the date as a number onto the sheet

In the beforeupdate event:
use the function format on the textbox
(so there is a standarized textstring)

indate.value = formatdatetime(InDate.value,vbshortdate)
or
indate.value = format(InDate.value,"dd/mm/yy")

In the afterupdate event:
then 'read' the different date aspects by using functions left, right and mid and set them to values in the function dateserial
the outcome is the serial number for the date.
place that value on the worksheet and the sheetformat will apply

dim iY as Integer
dim iM as Integer
dim iD as Integer

iY=val(right(InDate.value,2)
iM=val(mid(InDate.value,4,2)
iD=val(left(InDate.value,2)

Worksheets("Sheet1").Range("A1") = dateserial(iY,iM,iD)

maybe there is even a shorter way or a better way but I know this worked for me.

 

[Chats]

Hi littlewoman,

Thanks - this worked a treat.

Ade

 

[FaneDuru]

A1 has to be formatted as "dd/mm/yyy"
Put the next code in the form:

Private Sub InDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
 Dim x as String
 x = InDate.Text
   Worksheets("Sheet1").Range("A1") = Mid(x, InStr(x, "/") + 1, InStrRev(x, "/") - _
          InStr(x, "/") - 1) & "/" & Left(x, InStr(x, "/") - 1) & "/" & _
          Right(x, Len(x) - InStrRev(x, "/"))
End Sub

Fane Duru

 

[FaneDuru]

Sorry...
In this way you may use in the text box next format:
d/mm/yyyy
d/m/yyyy
d/mm/yy
and so on... and the result will be good.

Fane Duru