Need help with VBA date validation

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. 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!

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
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
Ah, sorry, I should remove "Me." from the sub.
Replace my code with this one:
VBA Code:
Private Sub DOBTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With DOBTextBox
If .Value = "" Then Exit Sub
If IsDate(.Value) Then
    .Value = Format(.Value, "m/d/yyyy")
Else
    MsgBox "Please enter a valid date, such as: 4-15-20 (month-day-year)"
    Cancel = True
    .Value = Empty
End If
End With
End Sub
Okay, Thank you soooo much!!! Now it works error-free and seems to do exactly what I need. I did have to remove the "enter" and "exit" part of the code that related to DOBTextBox but that's not that big of a deal.

I do have one further question. How can I make it so that the user can leave the DOB field blank and still use the OK button to send other fields to the worksheet??
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,479
Office Version
  1. 2019
Platform
  1. Windows
Hi,
here is another approach you can try

Place codes in your userform code page
VBA Code:
Private Sub DOBTextBox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    KeyAscii = IIf(Chr(KeyAscii) Like "*[!0-9/]*", 0, KeyAscii)
End Sub

Private Sub DOBTextBox_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Button_Enable Me, KeyCode
End Sub

ENSURE you DELETE any existing codes with same name & any other events used to test valid date entry in the textbox.

Place following code either in your userform code page or a standard module

VBA Code:
Sub Button_Enable(ByVal Form As Object, ByVal KeyCode As Integer)
    Dim ValidFormat As Boolean
   
    With Form.DOBTextBox
        .MaxLength = 10
        'Select Case KeyCode
        If KeyCode = vbKeyBack Or KeyCode = vbKeyTab Then
        'do nothing
        Else
         Select Case Len(.Text)
            Case 2, 5
            If Form.Visible Then .Text = .Text & "/"
        End Select
        End If
       
        ValidFormat = .Text Like "##/##/####" And IsDate(.Text) Or Len(.Text) = 0
       
        .BackColor = IIf(Len(.Text) = 0, vbWhite, IIf(ValidFormat, vbGreen, vbRed))
       
    End With
    Form.OKCommandButton.Enabled = ValidFormat
End Sub

Delete following part of code in your CommandButton

Rich (BB code):
If IsDate(Me.DOBTextBox.Text) = False Then
        MsgBox "Please enter a valid date!"
        Exit Sub
    End If

Codes will only allow Numeric values & / character to be entered in textbox & will disable the commandbutton until valid date ( or blank entry ) is entered
The / character is automatically added.

As always, make back-up before trying new code.

Hope helpful

Dave
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
@dmt32 Hi Dave, thanks so much for stepping in. I tried your code and I can definitely see this as a possible solution to my problem. However, as soon as I type a single digit in the DOB field, it turns red. I tried every possible date I could think of and it's always red and the OK button is disabled. Any idea why?
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,479
Office Version
  1. 2019
Platform
  1. Windows
@dmt32 Hi Dave, thanks so much for stepping in. I tried your code and I can definitely see this as a possible solution to my problem. However, as soon as I type a single digit in the DOB field, it turns red. I tried every possible date I could think of and it's always red and the OK button is disabled. Any idea why?

that's just an effect I added - if you enter valid date in format ##/##/#### textbox should turn green & commandbutton enabled

Backcolor effect can be removed if not wanted

Dave
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

@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.

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.

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.

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.

I really appreciate your help and for taking the time out of your day to do it. (y)
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,961
Office Version
  1. 365
Platform
  1. Windows
I do have one further question. How can I make it so that the user can leave the DOB field blank and still use the OK button to send other fields to the worksheet??
Try removing this part of your code:
VBA Code:
    If IsDate(Me.DOBTextBox.Text) = False Then
        MsgBox "Please enter a valid date!"
        Exit Sub
    End If


My code works like this:
User can type the date with any format they want but as long as it is a valid date format then (when the focus leaves the textbox) it will be converted to "m/d/yyyy" format which is what you want.
So the user doesn't have to know what date format to insert, they can type 10/23/87 or 10-23-87 or October 23, 87 or October 23, 1987 or anything else, if it is a valid date format then it will be converted to "m/d/yyyy" (i.e 10/23/1987).
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

@Akuini Brilliant! Works perfectly! Thank you so much. (y)

I have a question about your code. When I type 4/13/45 the result is 4/13/2045. Any dates prior to 1950 are showing up as in this century. Is there a mod that will prevent the code from doing this?
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,961
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
When I type 4/13/45 the result is 4/13/2045.
What should happen if the user does type 4/13/2045, is that allowed?
So, do you want to set the allowable year range? say 1941 to 2040? or what?
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
When I type 4/13/45 the result is 4/13/2045.
What should happen if the user does type 4/13/2045, is that allowed?
So, do you want to set the allowable year range? say 1941 to 2040? or what?
Well, I didn't really want to set an upper limit but how about if it was impossible to enter a date that was in the future. The user is entering a DOB so it's always going to be in the past. Not sure which approach is best but thought you might have some ideas.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,961
Office Version
  1. 365
Platform
  1. Windows
Maybe this:

VBA Code:
Private Sub DOBTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim dt As Date, tx As String
With DOBTextBox
If .Value = "" Then Exit Sub
    If IsDate(.Value) Then
        tx = .Value
        dt = Format(.Value, "m/d/yyyy")
        
        If dt > Date Then
            MsgBox "You've entered date: " & tx & " , it means " & Format(dt, "mmmm d, yyyy") & vbLf & _
            "which is not allowed because it's a future date." & vbLf & _
            "Try typing 4 digit year, such as: 3-4-1940"
            Cancel = True
        Else
            .Value = dt
        
        End If
    
    Else
        MsgBox "Please enter a valid date, such as: 4-15-1990 (month-day-year)"
        Cancel = True
        .Value = Empty
    End If
End With

End Sub
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,316
Messages
5,623,961
Members
416,002
Latest member
Neshx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top