Need help with VBA date validation

[leopardhawk]

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!

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

 

[dmt32]

@dmt32 ahhhh, I see. That's awesome! I like the idea of having the backcolors but I found them a bit harsh so I muted them using RGB(169,209,142) and RGB(255,55,55). A few questions:

1. I notice that while the code doesn't permit alpha characters or even a dash "-", it will allow the user to enter the "/" key. I know there are folks who will be so used to typing 12/09/1989 they'll just instinctively type it like that and I tested this, ending up with 12//0/9/19 if I type it really fast. Should I just ignore this and let them figure it out, not a big deal for me to leave it as is.

Code restricts users to numeric & / character only in an attempt to ensure that they only enter valid date values.
The auto adding of the / character is intended to guide users to enter date in certain format but if it could be an issue then you can either just remove auto entry feature from code or remove users ability to enter / & just let code do it.

2. Some users may not intuitively know to type leading zeroes on single-digit dates either which creates a minor issue as well. Don't get me wrong, I think this is great, just trying to think outside the box when it comes to how the General Public deals with this kind of thing. I'm trying to make the form idiot-proof if you'll pardon the expression.

You could add a tooltip text to control to prompt the user to enter date in required format.

3. Would it really screw things up to allow long dates, i.e. October 23, 1987? I guess that opens up another whole can o' worms, eh? The input would have to be validated somehow and then converted to 23/10/87.

Solution is designed for numeric date format ##/##/#### only

4. What about allowing double-digit year input. i.e. 10/23/87 or even October 23, 87? Again, I'm simply trying to learn as much here as I can.

If want to do this then you probably would just have code to format your textbox using the controls exit event

[/Quote]

something like following maybe

Private Sub DOBTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  With Me.DOBTextBox
    If IsDate(.Text) Then .Text = Format(DateValue(.Text), "dd/mm/yyyy")
  End With
End Sub

When user exits the control & If the text entered is recognized as a date, then it can be formatted as required.

VBA Help:
DateValue recognizes the order for month, day, and year according to the Short Date format that you specified for your system. DateValue also recognizes unambiguous dates that contain month names, either in long or abbreviated form. For example, in addition to recognizing 12/30/2020 and 12/30/20, DateValue also recognizes December 30, 2020 and Dec 30, 2020.



It is quite difficult to cover all scenarios for users entering a date in a textbox - suggestion is just an attempt to show an approach that can be used to limit users to entering numeric values & / character only & hopefully, gives you something you can build on.

Hope Helpful

Dave

 

[leopardhawk]

@dmt32 Thank you so much! Great answers, very helpful, and I appreciate it a lot.

 

[dmt32]

@dmt32 Thank you so much! Great answers, very helpful, and I appreciate it a lot.

Hi,
suggestion is when not using a date picker, just one idea for an approach you could take to try and ensure that you get a valid date entry in a textbox

Another idea could be to have three textboxes where users enter day / month ./ year

Hope Helpful

Dave
PS - sorry about format in last post - something went awry with the tags.