Dates Switching from Form to Cell

[scotball]

I have a form with 2 date fields which have some formatting options in the VBA code.

When I put in a date like 10/08/2018 in the form... when it puts the date in to the corresponding cell on submission, it's switching the date to 08/10/2018.

I've formatted the cells to dd/mm/yyyy and the code in the VBA is:

Private Sub CommandButton1_Click()    With DOBTextBox
        .Value = dDate
        .NumberFormat = "dd/mm/yyyy"
    End With
End Sub




Private Sub DOBTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    With Me.DOBTextBox
        If Len(.Text) > 0 Then
            If Not .Text Like "##/##/####" Then
                MsgBox "Invalid DOB date format" & Chr(10) & "Please re-enter as dd/mm/yyyy", vbCritical, "Invalid Format"
                Cancel = True
            Else
                dDate = DateValue(.Text)
            End If
        End If
    End With
End Sub


Private Sub CommandButton2_Click()
    With SessionsBox
        .Value = dDate2
        .NumberFormat = "dd/mm/yyyy"
    End With
End Sub




Private Sub SessionsBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    With Me.SessionsBox
        If Len(.Text) > 0 Then
            If Not .Text Like "##/##/####" Then
                MsgBox "Invalid Session date format" & Chr(10) & "Please re-enter as dd/mm/yyyy", vbCritical, "Invalid Format"
                Cancel = True
            Else
                dDate2 = DateValue(.Text)
            End If
        End If
    End With
End Sub

Any ideas on how to stop the numbers from switching please?

 

[steve the fish]

Try using CDate

Edit to be more clear, eg:

Range("A1").Value = CDate(TextBox1.Text)

 

[scotball]

Thanks Mr the fish

In my code I also have this:

Cells(emptyRow, 2).Value = DateValue(DOBTextBox.Value)
Cells(emptyRow, 3).Value = CDate(SessionsBox.Value)

When I tried CDate for DOBTextBox I was getting an error if memory serves me correctly.

 

[steve the fish]

What is the error?

 

[scotball]

I changed the code to this:

Cells(emptyRow, 2).Value = CDate(DOBTextBox.Text)
Cells(emptyRow, 3).Value = CDate(SessionsBox.Text)

and no errors but it's still switching dates... example:

Form DOB = 08/03/2015, what went into the cell= 03/08/2015
Form Session Date = 06/03/2017, what went into the cell = 03/06/2017

 

[steve the fish]

Change the cell to General. What is the number?

 

[scotball]

For DOB = 42219
For Session = 42889

 

[steve the fish]

Ok so the dates are indeed wrong. The locale is set to UK i presume? Format cells, date. I cant replicate your problem you see. I can if i dont convert the date but not once i convert the date. Its as if the application thinks you use MM/DD/YYYY on the machine. I could convert it manually if you want?

 

[scotball]

locale is set to UK - the office language preferences are set to English (UK)... tell me more about converting it manually... I need to get this working for my client lol

 

[steve the fish]

Heres a more manual way:

arrDate = Split(TextBox1.Text, "/")
If UBound(arrDate) = 2 Then Range("A1").Value = DateSerial(arrDate(2), arrDate(1), arrDate(0))