Thanks, Chris! I'll give this one a try.

"Chip Pearson" wrote:

You can try the following code. It allows in TextBox1 only numbers and
slashes. It will convert single-digit months and days to two digits. It will
automatically insert slashes at the appropriate location as you type. It
prevents text longer than 10 chars. Finally, it tests to see if the input is
a valid date.

Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)

Dim DT As Date
If KeyCode = vbKeyBack Then
Exit Sub
End If

With Me.TextBox1
If .Text Like "#" Then
' do nothing
ElseIf .Text Like "#/" Then
.Text = "0" & Left(.Text, 1) & "/"
ElseIf .Text = vbNullString Then
' do nothing
ElseIf .Text Like "##" Then
.Text = .Text & "/"
ElseIf .Text Like "##/" Then
' do nothing
ElseIf .Text Like "##/#" Then
' do nothing
ElseIf .Text Like "##/#/" Then
.Text = Left(.Text, 2) & "/" & "0" & Mid(.Text, 4, 1) & "/"
ElseIf .Text Like "##/##" Then
.Text = .Text & "/"
ElseIf .Text Like "##/##/####" Then
On Error Resume Next
Err.Clear
DT = DateValue(.Text)
If Err.Number < 0 Then
' invalid data
.SelStart = 0
.SelLength = Len(.Text)
End If
ElseIf .Text Like "##/##/####?*" Then
If Len(.Text) 0 Then
.Text = Left(.Text, Len(.Text) - 1)
End If
ElseIf Right(.Text, 1) Like "[!0-9/]" Then
.Text = Left(.Text, Len(.Text) - 1)
ElseIf .Text Like "[A-Za-z]*" Then
If Len(.Text) 0 Then
.Text = Left(.Text, Len(.Text) - 1)
End If
ElseIf Len(.Text) 10 Then
.Text = Left(.Text, 10)
End If
End With
End Sub


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


"Bowtie63" wrote in message
...
Hi! I have a question that I didn't seem to find in the threads. I'm using
Excel 2003. I have a form with a text box for users to enter a date. Is
there a way to make the text box set for date format and provide the
'##/##/####' for the users to see? I have the cells associated with this
field in the worksheet set in 10 digit date format. I would like to have
the
date template so that the users won't enter something different or
non-conforming. I know this can be done in an Access form, but I'm not
allowed to use Access because of corporate policy (you don't want to hear
the
explanation). I'll be anxious to hear your tips. Thanks!