CODEWITHSUNDEEP
excelinputboxvba
user3794203
user3794203

Reputation: 235

Excel Forms - entry

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

Upvotes: 0

Views: 128

Answers (1)

Axel Richter
Axel Richter

Reputation: 61852

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

Upvotes: 1

Related Questions