VBA Userform date format issue - dd/mm/yyyy

[Patrick69]

Dear experts,

I am trying to enter a European date in my userform but the result is always in US date format.

Here is the code I got from an internet template:
____________________________________________
Private Sub cmdEnter_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Forester X fuel")

''find first empty row in database
'revised code to avoid problems with Excel tables in newer versions
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

'check for a date
If Trim(Me.txtDate.Value) = "" Then
Me.txtDate.SetFocus
MsgBox "Please enter the date"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtDate.Value
ws.Cells(iRow, 2).Value = Me.txtOdo.Value
ws.Cells(iRow, 3).Value = Me.txtLitres.Value
ws.Cells(iRow, 4).Value = Me.txtCost.Value
ws.Cells(iRow, 7).Value = Me.txtLocation.Value

'copy the provider to the database
If frmFuel.obnBP.Value = True Then
ws.Cells(iRow, 6).Value = "BP"
Else
ws.Cells(iRow, 6).Value = "Caltex"
End If

'clear the data
Me.txtDate.Value = ""
Me.txtOdo.Value = ""
Me.txtLitres.Value = ""
Me.txtCost.Value = ""
Me.txtLocation.Value = ""
Me.obnBP.Value = False
Me.obnCaltex.Value = False
Me.txtDate.SetFocus

End Sub
___________________________________________

My spreadsheet is set to "ddd dd mmm yyyy" and I can enter a date (via the cell) in many ways, it always gets it right (eg. 1-9-12 or 1/9/12 or 1-9-2012 gives me "Sat 01 Sep 2012).
Now how can I tell VBA that the text I enter in my userform is just a string, hoping that it will be converted to my custom date format in my spreadsheet?

Many thanks for the help. Note: Complete VBA novice!
Patrick

 

[Jerry Sullivan]

Hello Patrick,

You can use the isDate() function to confirm that the string entered in the textbox can be converted to a date and the CDate() to do the conversion.

Here is an example...

With Me.txtDate
    If Not IsDate(.Value) Then
        .SetFocus
        MsgBox "Enter a valid date"
        Exit Sub
    End If
End With

'copy the data to the database
With ws.Cells(iRow, 1)
    .Value = CDate(Me.txtDate)
    .NumberFormat = "ddd dd mmm yyyy"
End With

Be aware that those functions use the Regional Settings that you have set up in your computer to interpret the meaning of the text entered in the textbox.
If you enter the date in the textbox in a form consistent with your regional settings, you will get the desired result in the worksheet cell.

 

[Patrick69]

Sorry it took me so long to get back to it... Many thanks! It works and the "valid date" is very elegant.
Now just in case someone is still reading, in column C I have my litre and in column D the cost. In column E I have the calculated $/litre that I use to copy down many rows in advance but if I do that using the "find the first free row" code, it obviously does not work. Could someone show me an example of code to copy the last occupied cell in column E to the cell just below?
Many thanks again, Patrick

 

[Jerry Sullivan]

Patrick,

You would want to paste your formula into Cells(iRow,5) instead of finding the first free row in Column E. Otherwise it might not align with the other data for that record.

Assuming that the cell above that has the formula you want to copy, you could use this code...

ws.Cells(iRow, 3).Value = Me.txtLitres.Value
ws.Cells(iRow, 4).Value = Me.txtCost.Value
[COLOR="#0000CD"][B]ws.Cells(iRow - 1, 5).Copy Destination:=ws.Cells(iRow, 5)[/B][/COLOR]

 

[Patrick69]

Many thanks to all. Excel is an amazing software and forums are unbelievably useful for beginners!
Regards, Patrick</SPAN>