Wrong date format insede a UserForm

[nicitalia]

Hi all folks,

I have a problem with the date format in a userform:

I've designed this simple UF to manage the annual expense about to car costs. The UF works well, you insert the type of cost, the ammount and the date of the cost.

Nut when I press the OK button to insert the data in the worksheet the date formate insede the third column isn't correct, because excel read the date in US way (I mean, the month before the day) and not in the EU way, even if I've assigned the correct numberformat to the third column that is
NumberFormat = "dd/mm/yyyy"

I realy can't understand why...maybe it's because of the spinbutton that I use to increse or decrease the days that containt the method CDate???

I attach a stamp of the running userform...I know it's in Italian but I think it's easy to undestand...let me know.

Thank you guys..

Here is the code:

Private Sub SpinButton1_Change()

Sub SpinButton1_SpinDown()
Me.TxtDate = CDate(Me.TxtDate) - Me.SpinButton1.SmallChange
End Sub

Sub SpinButton1_SpinUp()
Me.TxtDate = CDate(Me.TxtDate) + Me.SpinButton1.SmallChange
End Sub

With SpinButton1
.SmallChange = 1
.Min = 1
.Max = 100
End With
End Sub


Private Sub UserForm_Initialize()
Dim VoceSpesa As Variant
VoceSpesa = Array("carburante", "autostrada", "altro")
For i = 0 To UBound(VoceSpesa)
With Me.CboVoce
.AddItem VoceSpesa(i)
End With
Next i
Me.TxtDate.Value = Format(Date, "medium date")
End Sub


Private Sub CmdInserisci_Click()
Dim lRow As Long
Set ws = Worksheets("Spese")
'find first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
With ws
.Cells(lRow, 1).Value = Me.CboVoce.Value
If CheckBox1 = True Then Cells(lRow, 1).Font.ColorIndex = 4
If CheckBox2 = True Then Cells(lRow, 1).Font.ColorIndex = 3
If CheckBox3 = True Then Cells(lRow, 1).Font.ColorIndex = 5
.Cells(lRow, 2).Value = Me.TxtCommento.Value
.Cells(lRow, 3).Value = Me.TxtDate.Value
.Cells(lRow, 3).NumberFormat = "dd/mm/yyyy"
.Cells(lRow, 4).NumberFormat = "$ #.##0"
.Cells(lRow, 4).Value = Me.TxtValue.Value
End With
End Sub

 

[nicitalia]

I'm not able to upload the image of the worksheet and xls file..-can someone tell me how to do it?

thank you

Nic

 

[SkipVought]

Hi,

ANY, ANY ANY conversion that takes place from text to date, WILL, WILL, WILL assume the US m/d/yyyy structure in the text, after all, Gates' creation was in Washington, USA!

The BEST way to assure that your date text will convert properly, is to use the UNAMBIGUOUS, yyyy/mm/dd text structure.

I would NEVER assume a particular structure. Rather, use a YEAR textbox, MONTH textbox and DAY textbox. Then convert these text values using the DateSerial() function.

Once converted, you can DISPLAY the date in any format you choose.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

It is possible to use 'local' for interactions between userform and worksheet:

[tt].Cells(lRow, 3).FormulaLocal = Me.TxtDate.Text[/tt]

combo

 

[nicitalia]

@ skip: thank you for your great answer...(after all, Gates' creation was in Washington, USA!) lol
I didn't know...anyway, I've solved the problem putting insted of
.Cells(lRow, 3).Value = Me.TxtDate.Value
this one
.Cells(lRow, 3).Value = CDate(Me.TxtDate.Value)
and now it works...don't know why but it works...

I was also thinking to use the method you suggest:
"Rather, use a YEAR textbox, MONTH textbox and DAY textbox. Then convert these text values using the DateSerial() function."

I don't know the DataSerial function but I'll give it a try.

Thank you so much...

@combo: thank you now I'll try to use it!

 

[nicitalia]

Hi Skip,

I forgot to ask a you a question, about the way you suggest to
insert the date.

If I use three different combobox to insert tha day, the month and the year, how could I obtain a system that could take in occount of the leap years and of the month, like the form that we found on some website?
Eg: if in the month combobox "sempterber" is selected, I want that in the day combobox the maximum number is 30.
It's the same way like when we must fill a form a we select from a combox the state where we live and in the combobox of the city we can choose only between the cities of that state:
eg:
California --> Sacramento, Los Angeles, San Diego, San Francisco, Fresno...

thank you!!!