Date Formats in Text Box
 

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!

Date Formats in Text Box
 

Bowtie63

You have some information conflicks. Is this a "Text Box" or a cell where
text is entered. If it is a cell I would GOTODataValidation. In the
"settings tab" select the drop down arrow for "Allow" and select "date".
Under the input message tab I would explain that only a date is to be entered
here. Under the Error tab I would again explain that nothing else will ever
be accepted, and "OK" out. Now the only thing that you need to do is set your
formating (under "formate cell"). Now only a date with the assigned
formating will be seen in that cell.

Mike Rogers

"Bowtie63" wrote:

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!

Date Formats in Text Box
 

Bowtie63

Forgot to say when you select "date" it will ask you for a range of dates.
I would enter dates here that are beyond the possibilities of selection by
those inputting data. ie 01/01/1950 to 12/31/2100

Mike Rogers

"Bowtie63" wrote:

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!

Date Formats in Text Box
 

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!

Date Formats in Text Box
 

Thanks, Mike! It's a text box. It's part of a form created in VB. Thanks!

"Mike Rogers" wrote:

Bowtie63

You have some information conflicks. Is this a "Text Box" or a cell where
text is entered. If it is a cell I would GOTODataValidation. In the
"settings tab" select the drop down arrow for "Allow" and select "date".
Under the input message tab I would explain that only a date is to be entered
here. Under the Error tab I would again explain that nothing else will ever
be accepted, and "OK" out. Now the only thing that you need to do is set your
formating (under "formate cell"). Now only a date with the assigned
formating will be seen in that cell.

Mike Rogers

"Bowtie63" wrote:

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!

Date Formats in Text Box
 

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!

Date Formats in Text Box
 

Chip. Chip Pearson.

Bowtie63 wrote:

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

"Chip Pearson" wrote:

<<snipped

Date Formats in Text Box
 

Oops, my mistake. Thanks Chip!

"Dave Peterson" wrote:

Chip. Chip Pearson.

Bowtie63 wrote:

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

"Chip Pearson" wrote:

<<snipped