Alternative is to enforce numbers only input in the textbox, with exact length.
Ex: Assuming always mmddyyyyhhmm format (24 hour clock). 12 char length.
040720201100
Then use Format() function to format it to date time string, then if needed you can use CDate on it to convert to datetime value.
Sample code:
In textbox KeyPress event...
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii >= 48 And KeyAscii <= 57 Then
Else
MsgBox "Only numbers accepted"
KeyAscii = 0
End If
End Sub
Then in some other command button (i.e. when you submit form data to sheet etc).
Private Sub CommandButton1_Click()
If Len(TextBox1.Value) <> 12 Then
MsgBox "Must be 12 character long and in mmddyyyyhhmm format and hour must be in 24 hour clock"
TextBox1.SetFocus
Else
TextBox1.Value = CDate(Format(TextBox1.Value, "00\/00\/0000\ 00\:00"))
End If
'Rest of your code
End Sub
Now this won't accommodate for users typing in yyyymmddhhmm or some other format. You could validate each key press for valid value only... but that's bit more complicated.
Ex: Char position 1 must be only 0 or 1, position 2 must be 0-2 if char1 is 1, 0-9 if char1 is 0 etc...
Bookmarks