leopardhawk
Well-known Member
- Joined
- May 31, 2007
- Messages
- 611
- Office Version
- 2016
- Platform
- Windows
Hello forum friends,
I could sure use some suggestions on how to best force my users to enter a valid date in a 'Date of Birth' field on a UserForm before they click the OK button. The code sends the date to Sheet11 (E9) and while it rejects some input, it accepts other input which creates a problem on Sheet11 (E9) where cell E9 is formatted as DATE.
In the examples below, the output to cell E9 should be 12/4/1966. In fact, it NEEDS to be 12/4/1966.
So, if the input is:
4/12/66 - accepted - output= 12/4/1966
4 12 66 - accepted - output= 4 12 66 (should be rejected)
4-12-66 - accepted - output= 12/4/1966
april 12 66 - accepted - output= april 12 66 (should be rejected)
april 12 1966 - accepted - output= april 12 1966 (should be rejected)
april 12, 1966 - accepted - output= 12/4/1966
1966/4/12 - accepted - output= 12/4/1966
apr 12, 1966 - accepted - output= 12/4/1966
ap 12, 1966 - rejected - output= MsgBox
1966 April 12 - accepted - output= 1966 April 12 (should be rejected)
There may be lots of others but you can see that there are many instances where the input should be rejected and the MsgBox should pop up advising the user to "Please enter a valid date!" but it is oftentimes accepting this input instead of rejecting it. This is what I am hoping to find a way to correct. I appreciate any ideas. Thanks!
I could sure use some suggestions on how to best force my users to enter a valid date in a 'Date of Birth' field on a UserForm before they click the OK button. The code sends the date to Sheet11 (E9) and while it rejects some input, it accepts other input which creates a problem on Sheet11 (E9) where cell E9 is formatted as DATE.
In the examples below, the output to cell E9 should be 12/4/1966. In fact, it NEEDS to be 12/4/1966.
So, if the input is:
4/12/66 - accepted - output= 12/4/1966
4 12 66 - accepted - output= 4 12 66 (should be rejected)
4-12-66 - accepted - output= 12/4/1966
april 12 66 - accepted - output= april 12 66 (should be rejected)
april 12 1966 - accepted - output= april 12 1966 (should be rejected)
april 12, 1966 - accepted - output= 12/4/1966
1966/4/12 - accepted - output= 12/4/1966
apr 12, 1966 - accepted - output= 12/4/1966
ap 12, 1966 - rejected - output= MsgBox
1966 April 12 - accepted - output= 1966 April 12 (should be rejected)
There may be lots of others but you can see that there are many instances where the input should be rejected and the MsgBox should pop up advising the user to "Please enter a valid date!" but it is oftentimes accepting this input instead of rejecting it. This is what I am hoping to find a way to correct. I appreciate any ideas. Thanks!
VBA Code:
Private Sub OKCommandButton_Click()
If UCase(Me.GenderComboBox.Text) = "M" Or UCase(Me.GenderComboBox.Text) = "F" Then
Else
MsgBox "Please select M or F from the list."
Exit Sub
End If
If IsDate(Me.DOBTextBox.Text) = False Then
MsgBox "Please enter a valid date!"
Exit Sub
End If
With Sheets(11)
If Not AllmostEmpty(FirstNameTextBox) Then .Range("C9").Value = FirstNameTextBox.Value
If Not AllmostEmpty(FirstNameTextBox) Then .Range("B15").Value = FirstNameTextBox.Value
If LastNameTextBox <> "Optional" Then
If Not AllmostEmpty(LastNameTextBox) Then .Range("D9").Value = LastNameTextBox.Value
End If
If DOBTextBox.Text <> "Use long date i.e. May 6, 1951" Then
If Not AllmostEmpty(DOBTextBox) Then .Range("E9").Value = DOBTextBox.Value
End If
If Not AllmostEmpty(GenderComboBox) Then .Range("F9").Value = GenderComboBox.Value
End With
Unload Me
End Sub