Sure, this is very quick and dirty. Date format should be read from the
regional settings, the one in this demo is pretty valid here in northern
europe, so tabbing doesn't destroy it. It must of course be something
Datevalue can read wherever one is.

But it is pretty much the way a cell confirms a date entry; by altering the
format slightly on exit.

Best wishes Harald

"Rick Rothstein" wrote in message
...
Well, okay, that does give the user feedback allowing them to correct a
bad entry (assuming they look at it), but I still think a calendar control
is a better way to go. By the way, after you enter a date and exit the
TextBox, put your cursor back in the TextBox and then exit it without
changing anything... you might want to use a more conventional date format
to prevent that.

--
Rick (MVP - Excel)


"Harald Staff" wrote in message
...
Hi Rick

I love *NO WAY* statements <gd&r. A date is a date is a date. Simplified
(this is better done subclassing a textbox i an class module):

Option Explicit

Dim Dt1 As Date

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dt1 = 0
On Error Resume Next
Dt1 = DateValue(Me.TextBox1.Text)
On Error Goto 0
If Dt1 < 100 Then
Me.TextBox1.Text = ""
Else
Me.TextBox1.Text = Format(Dt1, "d.mmmm yyyy")
End If
End Sub

Best wishes Harald

"Rick Rothstein" wrote in message
...
There will be *no* way you can tell if the date is in dd/mm/yy or
mm/dd/yy format for month and day combinations less than or equal to 12.
For example, if I entered 4/6/09 into your TextBox, how would *any*
routine know whether I meant April 6th or June 4th? Your best bet is to
use a calendar control of sort where your users can pick the date from a
monthly display.

--
Rick (MVP - Excel)


"zak" wrote in message
...
Hi

I was wondering if anyone can let me know how to add validation to text
input boxes on forms?

I have several forms on a spreadsheet that I am creating and there are
quite
a few time and date input boxes, which I was hoping I could add
validation to
so that the information is entered in the correct format? i.e. date
should
only be in dd/mm/yy, time should only be entered like hh:mm etc.

Can this be done?