VBA Date Format Switching

[scotball]

I have the following code:

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

When the Form runs and I put a date in, it works fine - however when it comes to this part:

Sheet1.ActivateCells(emptyRow, 1).Value = NameTextBox.Value
Cells(emptyRow, 2).Value = DOBTextBox.Value
Cells(emptyRow, 3).Value = SessionsBox.Value
Cells(emptyRow, 4).Value = GroupListBox.Value

For some of the values for DOBTextBox and SessionsBox the date flips to the US Format. Example:

DOB inputted on form = 06/03/2008
DOB copied to Excel cell = 03/06/2008

I can't see where this needs to be fixed to ensure the correct date format is copied into the cells - this is important as it has to be the correct day and month for DOB and Session.

Thanks in advance,
Lee

 

[Norie]

Lee

When putting 'text' dates from a textbox on a userform into a cell use DateValue or CDate to convert the values to 'real' dates.

Sheet1.Activate
Cells(emptyRow, 1).Value = NameTextBox.Value
Cells(emptyRow, 2).Value = DateValue(DOBTextBox.Value)
Cells(emptyRow, 3).Value = CDate(SessionsBox.Value)