CODEWITHSUNDEEP
excelvbadate
Sven
Sven

Reputation: 31

Textbox converts date into number

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

    StartDate = DateSerial(Year(Date), Month(Date), Day(Date))
    TextBox1.Value = Format(TextBox1.Value, "dd.mm.yyyy")
    StartDate = TextBox1.Value
End Sub

This code works well for me and my colleagues to make sure the value entered in the textbox is a date. However, a colleague from a different country (but also with the dd.mm.yyyy date-format) gets weird results.

For example: If he enters 01.10.2017 the TextBox automatically format the date into 20.03.4917.

I suspect that in this case the entered value is not recognized as a date but as a number because 01102017 will transform into 20.03.4917 in Excel if you convert it as a date.

Does anyone have a suggestion or a guess how to work around this problem?

Thanks and best regards

Upvotes: 0

Views: 240

Answers (2)

simple-solution
simple-solution

Reputation: 1139

I do believe that your colleague enters the text string "01102017" without dots.
You might want to convert such kind of entries into a valid date:

'  "01102017" => 01.10.2017      - 8 chars variant
'  "011017"   => 01.10.2017      - 6 chars variant

This needs to be done of cause prior to a date conversion.


Add a log of the input value to a free cell range and
have the workbook sent back to you:

...
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Range("Z1").value = "'" & TextBox1.Value
    If Mid(TextBox1.Value, 4, 2) > 12 Then
...




Check if only numbers are used:
How to check if a string contains only numbers?

and check the length of the string (6 or 8 chars variant) and check if the year part is within a valid range => try to convert to a date and offer it to the user.

Upvotes: 1

Pᴇʜ
Pᴇʜ

Reputation: 57753

You could split the date by . into an array ArrInput and then use DateSerial to make it a real date, that you can format.

Dim ArrInput As Variant
ArrInput = Split(TextBox1.Value, ".")

'StartDate = DateSerial(Year(Date), Month(Date), Day(Date)) 'I see no use in this at all
TextBox1.Value = Format$(DateSerial(ArrInput(2), ArrInput(1), ArrInput(0)), "dd.mm.yyyy")
StartDate = TextBox1.Value

The issue with Format(TextBox1.Value, "dd.mm.yyyy") is that here you let Excel guess which date format the string in TextBox1.Value is. It automatically casts into a number which is then converted into a string again.

To avoid date misunderstandings I recommend always to use the YYYY-MM-DD format according to ISO 8601. This is the only date format that is human readable and cannot be misunderstood. It also has benefits when sorting by dates that are actually strings.

To make your validation even more solid use something like:

Dim ArrInput As Variant
ArrInput = Split(TextBox1.Value, ".")

Dim ValidDate As Boolean

If UBound(ArrInput) = 2 Then 'make sure there are exactly two dots in the date
    If ArrInput(1) > 0 And ArrInput(1) <= 12 And _ 'month <= 12
    ArrInput(0) > 0 And ArrInput(0) <= 31 Then     'day <= 31
        ValidDate = True
    End If
Else
    ValidDate = False
End If

If Not ValidDate Then
    MsgBox "Invalid date, please re-enter in format dd.mm.yyyy", vbCritical
    TextBox1.Value = vbNullString
    TextBox1.SetFocus
    Exit Sub
End If

TextBox1.Value = Format$(DateSerial(ArrInput(2), ArrInput(1), ArrInput(0)), "dd.mm.yyyy")
StartDate = TextBox1.Value

Upvotes: 2

Related Questions