| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Formats in Text Box
Chip. Chip Pearson.
Bowtie63 wrote: Thanks, Chris! I'll give this one a try. "Chip Pearson" wrote: <<snipped |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date Formats | Excel Discussion (Misc queries) | |||
| The Cell formats keep changing itself from text to date | Excel Discussion (Misc queries) | |||
| Date Formats | Excel Discussion (Misc queries) | |||
| Text, Number and Date formats excel 2003 driving experienced user | Excel Discussion (Misc queries) | |||
| Can you retain different text formats when merging text? | Excel Discussion (Misc queries) | |||
Linear Mode
