Change date in Userform to "dd/mm/yy"

[manny88]

Hi Guys,

I am having issues with the date appearing the way I need it to in my userform textbox. At the moment any entry that I make as "dd/mm/yyyy" is converted to the spreadsheet as "mm/dd/yyyy"

i.e 09/06/2020 changes to 06/09/2020

The code I am using is this directly below

Cells(EmptyRow, 4).value = DateTextBox.Value

I have tried a few variations of adding DateTextBox.Value = Format(DateTextBox.Value, "dd/mm/yyyy") without success.

Can you help?

P.S. a bonus help would be something to prevent date entry being entered in any other way to ensure consistency in the data

 

[dmt32]

Try

Cells(EmptyRow, 4).Value = DateValue(DateTextBox.Value)

Note - DateValue Function will error if no valid date exists in your textbox
Function recognises the order of day, month year according to the short date format specified in your system which should be your bonus - maybe!

Dave

 

[manny88]

Try

Cells(EmptyRow, 4).Value = DateValue(DateTextBox.Value)

Note - DateValue Function will error if no valid date exists in your textbox
Function recognises the order of day, month year according to the short date format specified in your system which should be your bonus - maybe!

Dave

How was it so simple?! Thank you so much, Dave!

 

[manny88]

Regards to the bonus question what I was thinking is like a check with a possible is function , perhaps.

So if the date is entered in the American format it would reject with a error/message box until it is entered as "dd/mm/yyyy"

 

[dmt32]

How was it so simple?! Thank you so much, Dave!

Excel VBA full of hidden gems - just ensure you manage the possibility of an invalid date (including empty textbox)

Many thanks for feedback

Dave

 

[dmt32]

Regards to the bonus question what I was thinking is like a check with a possible is function , perhaps.

So if the date is entered in the American format it would reject with a error/message box until it is entered as "dd/mm/yyyy"

you could try something like following & see if helps

Private Sub DateTextBox_AfterUpdate()
    Dim MyDate As Date
    With DateTextBox
        MyDate = DateSerial(Year(.Value), Month(.Value), Day(.Value))
    End With
    DateTextBox.Value = Format(MyDate, "dd/mm/yyyy")
End Sub

Dave

 

[dmt32]

neglected to add that suggestion most likely will some shortcomings but if you want to fully ensure date values get entered in correct order then as an idea, you could add 3 textboxes on your userform for day month & year - then just combine the values in to date format.

Dave

 

[manny88]

neglected to add that suggestion most likely will some shortcomings but if you want to fully ensure date values get entered in correct order then as an idea, you could add 3 textboxes on your userform for day month & year - then just combine the values in to date format.

Dave

Thanks Dave. I'm going to give that tip a go!

However, the issue this has now created is if the user wants to enter "N/A", the code DateValue(DateTextBox.Value) doesn't allow it comes up as a 'type mismatch'

Additionally,

Cells(empty Row, 13.Value = Format(Now, "dd/mm/yyyy HH:mm" intended as a timestamp of the entry is also reversing the day and months

 

[dmt32]

Thanks Dave. I'm going to give that tip a go!

However, the issue this has now created is if the user wants to enter "N/A", the code DateValue(DateTextBox.Value) doesn't allow it comes up as a 'type mismatch'

I did point out in my post that DateValue function will error if no valid date exists in textbox
If you are going to allow invalid values to be entered then you will need to update code to manage this - you can use the IsDate function to resolve

something like following maybe

With DateTextBox
    If IsDate(.Value) Then
       Cells(EmptyRow, 4).Value = DateValue(.Value)
    Else
       Cells(EmptyRow, 4).Value = .Value
     End If
  End With

Additionally,

Cells(empty Row, 13.Value = Format(Now, "dd/mm/yyyy HH:mm" intended as a timestamp of the entry is also reversing the day and months

You can apply NumberFormat to range & see if this resolves your issue - e.g.

With Cells(EmptyRow, 13)
    .Value = Now()
    .NumberFormat = "dd/mm/yyyy HH:mm"
 End With

Dave

 

[manny88]

Dave,

Thank you. That worked a charm!

Manny