Excel Forms - entry

Question

Is there a way to have a date input field automatically input a "/" between the month/day/year?
My current form has subs to ensure that the date is entered in a correct date format and it also checks to make sure that the entry is written as mm/dd/yyyy, however I wanted to do away with the user needing to manually input the "/" altogether and have not found a workable solution in my research and testing. Any helpful solutions are greatly appreciated.

Thank you!

    If Me.TourDateText.Value = "" Then
    MsgBox "Please enter Tour Date.", vbExclamation, "frmEntry"
    Me.FirstNameText.SetFocus
    Exit Sub
    End If

    With Me.TourDateText
    If Not IsDate(.Value) Then
    .SetFocus
    MsgBox "Enter a valid date"
    Exit Sub
    End If
    End With

    If Not IsDate(Me.TourDateText.Value) Then
    MsgBox "The Tour Date field must contain only dates in the format mm/dd/yyyy.", vbExclamation,  "frmEntry"   
    Me.TourDateText.SetFocus    
    Exit Sub
    End If

look at the `Textbox_Change` event. You can use it to parse the text as it is entered. — Cor_Blimey, Sep 16 '14 at 19:03
I would recommend using three separate text boxes: one each for *month*, *day*, and *year*. This is not unusual, and actually I think it is probably expected, from user perspective. Very rarely do you see form inputs that ask for *formatted dates*, precisely because it is more difficult to validate them, etc. Much easier to validate them separately, and also easy on your end to concatenate them to a date. — David Zemens, Sep 16 '14 at 19:27
Would [THIS](http://stackoverflow.com/questions/12012206/formatting-mm-dd-yyyy-dates-in-textbox-in-vba/12013961#12013961) help? — Siddharth Rout, Sep 16 '14 at 19:37
Thank you David Zemensand Siddharth Rout - both of your suggestions are helpful - however, as this user form is used on multiple devices, the datepicker widget is not ideal because I would need to download the file on multiple computers with strict downloading policies, and having 3 separate boxes would require a complete overhaul of a workbook that has taken a month to compile... The Textbox_Change event does sound promising, @Cor_Blimey - have you used this method before and can you suggest where I could find some examples? Thank you all! — user3794203, Sep 16 '14 at 21:09
@AxelRichter shows an example with the KeyPress event - this is superior to a quick hack in the _Change event I suggested as it can validate before text is committed to the text box rather than being fired after a change — Cor_Blimey, Sep 17 '14 at 17:39

Axel Richter · Accepted Answer · 2014-09-19T04:40:33.007

1

You could have then following into the UserForm

Private Sub TourDateText_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

 Select Case KeyAscii
  Case Asc("0") To Asc("9")
  Case Else
   KeyAscii = 0
 End Select
 If Len(Me.TourDateText.Text) = 2 Then
  If Val(Me.TourDateText.Text) > 12 Then KeyAscii = 0 Else _
   Me.TourDateText.Text = Me.TourDateText.Text & "/"
 End If
 If Len(Me.TourDateText.Text) = 5 Then
  If Val(Mid(Me.TourDateText.Text, 4, 2)) > 31 Then KeyAscii = 0 Else _
   Me.TourDateText.Text = Me.TourDateText.Text & "/"
 End If
 If Len(Me.TourDateText.Text) >= 10 Then KeyAscii = 0

End Sub

Second version:

Private Sub TourDateText_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

 Debug.Print KeyAscii

 If KeyAscii < 48 Or KeyAscii > 57 Then KeyAscii = 0
 If Len(Me.TourDateText.Text) = 2 Then
  If Val(Me.TourDateText.Text) > 12 Then KeyAscii = 0 Else _
   Me.TourDateText.Text = Me.TourDateText.Text & "/"
 End If
 If Len(Me.TourDateText.Text) = 5 Then
  If Val(Mid(Me.TourDateText.Text, 4, 2)) > 31 Then KeyAscii = 0 Else _
   Me.TourDateText.Text = Me.TourDateText.Text & "/"
 End If
 If Len(Me.TourDateText.Text) >= 10 Then KeyAscii = 0

End Sub

Now the user can input 09172014 and the Text will be 09/17/2014. Or the input can be 09[any key]17[any key]2014 and the Text will be 09/17/2014.

Also months > 12 and days > 31 will be prevented. This is not the final data validation because it can not check if the month has 31 days. But the final data validation have you already.

Greetings

Axel

edited Sep 19 '14 at 04:40

answered Sep 17 '14 at 06:35

Axel Richter

56,077
6
60
87

Dear @AxelRichter, thank you for your help! I have attempted to enter this sub within my form's code and the TourDateText field now no longer accepts input. I can select the input box but no numerical or text input is being allowed. Would you know a solution to this? – user3794203 Sep 17 '14 at 20:19
Thank you for your help, @AxelRichter. I am using Excel 2013 to write this UserForm and the control is an inputbox. I have been entering numbers with the numeric pad and my Num Lock is currently switched on... I am not sure why it is not allowing data entry. – user3794203 Sep 18 '14 at 20:26
I apologize for my shallow understanding, I have only been writing and editing in vba for about 3 months and have taught myself through trial and error. The ASCII codes that were returned in the VBA editor after entering in your updated sub were: 48 (0), and 57 (9). – user3794203 Sep 20 '14 at 14:46
Hm, and the 0 and the 9 were not visible in the TextBox? If not, please place the `Debug.Print KeyAscii` at the end of the code immediately before `End Sub` and try again with enter 0 and 9. Which ASCII codes were printed? – Axel Richter Sep 20 '14 at 15:01
with the ASCII Debug.Print KeyASCII at the end of the sub, the Immediate dialogue box reads all "0" – user3794203 Sep 22 '14 at 19:55
OK, do you understand my debugging tries? Do you understand, what the code does? What in the code sets KeyAscii to 0? Shift the Debug.Print upwards until KeyAscii 48 and 57 is printed again. My suspect: Len(Me.TourDateText.Text) >= 10 is true from begin on. So is there already content in the TextBox before you enter the date? – Axel Richter Sep 23 '14 at 05:55
thank you for your patience. You have solved it! The userform had inactive text "mm/dd/yyyy" within the input box that had previously allowed users to enter in dates, but with the addition of the new code, was not allowing for more entries. I went ahead and deleted the Properties TourDate text and now the form allows for entry and automatically populates the "/" between month/day/year. Thank you for your help! – user3794203 Sep 23 '14 at 14:27

Excel Forms - entry

1 Answers1