Reputation: 31
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
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
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