Capturing date from form in UK Format

[TheAbandoned]

Hello Everyone, i am struggling with a basic problem and can't figure out how to resolve it. I have a basic form that collects 4 pieces of information, one of them being the date of input which the user creates (most likely a more efficient way of doing this but they may want to back date information). When the data is transferred to the table it is formatted as US MM/DD/YYYY instead of DD/MM/YYYY which is how we like to do thing in the UK.

I am not sure how i attach the workbook or if indeed i can so below is the code i have in my VBA script.

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Mid(TextBox1.Value, 4, 2) > 12 Then
        MsgBox "Invalid date, please re-enter", vbCritical
        TextBox1.Value = vbNullString
        TextBox1.SetFocus
        Exit Sub
    End If
     
    dDate = DateSerial(Day(Date), Month(Date), Year(Date))
    TextBox1.Value = Format(TextBox1.Value, "DD/MM/yyyy")
    dDate = TextBox1.Value
End Sub


Private Sub CommandButton1_Click()
    Dim r As Long
    
    r = Application.WorksheetFunction.CountA(Worksheets("MASTER").Range("A:A")) + 1
       
    ' Insert the DATE
     Worksheets("MASTER").Cells(r, 1) = TextBox1.Text
    ' insert the Available bed days
     Worksheets("MASTER").Cells(r, 2) = ComboBox1.Value
    ' insert the Occupied Bed Days
     Worksheets("MASTER").Cells(r, 3) = ComboBox2.Value
     ' insert the Waiting list number
     Worksheets("MASTER").Cells(r, 4) = ComboBox3.Value
     
          
    Unload Me
    
    MsgBox "Your record has been added"


End Sub

I expect this is really simple but i can't figure it out. Any help or guidance very much appreciated.

Rob

 

[RoryA]

First, DateSerial's arguments are Year, Month, Day in that order, so this is wrong:

dDate = DateSerial(Day(Date), Month(Date), Year(Date))

All you need really is:

dDate = Date

but you overwrite that immediately with the textbox contents, so I don't really see the point of it.

Anyway, in terms of getting the date into the worksheet, you should use CDate:

' Insert the DATE
     Worksheets("MASTER").Cells(r, 1) = CDate(TextBox1.Text)

 

[TheAbandoned]

Thank you, it really was easy wasn't it... Sorry for asking such a newbie question.

Rob

 

[RoryA]

No problem - you are by no means the first person to get stuck on that particular issue!