Canned VBA Date Validation

[Kevin]

I want to validate a date entered into a textbox on a
form, before committing it to the target cell. This is
because the cell date format doesn't seem to append the
year if a date like 1/1 is entered (for some reason even
though the target cell is formatted as a date, when the
textbox value is copied into the cell, the format doesn't
change the entry, as it would when manually entered). If
either the cell formatting or some easy VB validation
script can do this, it would be ideal. The goal is to
have the entry in dd/mm/ccyy format for consistency.

I could write something to parse the input and either
append the year or return the date to the user for
correction, but I was hoping there is something canned out
there so I don't re-invent the wheel.

thanks

Kevin

 

[Jake Marx]

Hi Kevin,

You can use the IsDate function to see if a text string "looks like" a date
to VBA. Then you can use Format$() to format the string in the way you'd
like. Here's a simple example:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With TextBox1
If IsDate(.Text) Then
'/ format it
.Text = Format$(.Text, "mm/dd/yyyy")
Else
'/ error
MsgBox "The value you entered is not a date."
.SelStart = 0
.SelLength = Len(.Text)
Cancel = True
End If
End With
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Jim Rech]

for some reason even though the target cell is formatted as a date, when
the
textbox value is copied into the cell, the format doesn't
change the entry, as it would when manually entered).


You should do a Range("XX").Value = Textbox.Value rather than a copy/paste

 

[onedaywhen]

If available, it is much more pleasant to use a dedicated control. For
example, a quick look on my machine reveals the Microsoft Date and
Time Picker Control 6.0 (MSDATGRD.OCX). Before using in a solution,
ensure any controls you want to use are available on the client
machine or that you are able to distribute them.

 

[Rob van Gelder]

If not isdate(TextBox1.Text) then exit sub