Hi Everyone,

I'm sure that this is something simple that I am doing wrong but it is driving me to distraction.

I have a form on which I select a date (using a calendar form) and on the form it works perfectly. The date is in the correct dd/mm/yyyy format for example (03/04/2013 - 3rd April 2013), I then insert this date into a worksheet and it comes out in the correct format, but it is actually turning the dates around so where it should be putting 3rd of April (03/04/2013), it's inserting 4th March (04/03/2013) instead. It looks very much like the form sees the date in dd/mm/yyyy format and the worksheet when taking the data from the form thinks it in US format (mm/dd/yyyy).

This renders it completely useless. I have posted part of my code below, please help me understand what mistake I am making.

Calendar.Show
TextDate.Value = Format(TempDate, "dd/mm/YYYY")

'Open Lead Log to Reference
    Dim Workbook As String
    Workbook = "R:\hLog\Databases\" & "hLog - Appointment Log" & ".xlsm"
    On Error Resume Next
    Workbooks.Open (Workbook), UpdateLinks:=xlUpdateLinksAlways

    'Name Worksheets for ease of reference
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Worksheets("Appointment Log")

If Trim(Me.TextDate.Value) = "" Then
      Me.TextDate.SetFocus
      MsgBox "Please enter a valid date"
      Exit Sub
    End If
    
    MsgBox (Format(Me.TextDate.Value, "Long Date")) ' This is just something I inserted to check that it was in the correct format (which it is)
    'copy the data to the database
    ws.Cells(iRow, 1).Value = Me.TextDate.Value