Encountering Runtime Error When Entering Date in Textbox

[pmgibs]

I have the following code that upon exit from Textbox87 first checks to make sure the user input a date in the correct format and that it is a valid date. Then it checks to see if it is a past date or a future date. I receive a Runtime Error 13 Type Mismatch error only if a nonsensical date (i.e. 888888) is input in textbox87 and the msgbox "enter a valid date" appears. The error occurs after clicking Ok on the message box. When I click on Debug. The If CDate(TextBox87.Value) < Date Then is highlighted towards the end of the code. If a valid date is entered the error is not generated.

Private Sub TextBox87_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If TextBox87.Value = "" Then
    ElseIf Len(TextBox87) <> 6 Then
        MsgBox "Enter expiration date as mmddyy(eg. 021522)"
        Cancel = True
        If Cancel = True Then
        TextBox87.Value = ""
        Else
        End If
    End If
    Dim DateStr As String
        With Me.TextBox87
            Select Case Len(.Value)
            Case 6    ' e.g., 090298 = 2-Sep-1998
                DateStr = Left(.Value, 2) & "/" & _
                          Mid(.Value, 3, 2) & "/" & Right(.Value, 2)
            Case Else
                Exit Sub
            End Select
            .Value = DateStr
        If IsDate(TextBox87.Value) Then
        Else: MsgBox "Enter a valid date!"
        Cancel = True
        If Cancel = True Then
        TextBox87.Value = ""
        Else
        End If
        End If
        End With
    
    If IsDate(TextBox87.Value) And ComboBox35.Text <> "*Bottle" Or ComboBox35.Text <> "*Saline" Then
            If CDate(TextBox87.Value) < Date Then
            MsgBox "Saline " & ComboBox35.Text & " at Workstation " & ComboBox39.Text & " Expired!", vbCritical
            If CDate(TextBox87.Value) < Date Then
            TextBox87.BackColor = &H8080FF
            End If
            If CDate(TextBox87.Value) >= Date Then
            TextBox87.BackColor = &HFFFFFF
            End If
    End If
    End If
    End Sub

I have a very similar code that does not generate this error when a nonsensical date is entered into a different textbox. Here is that code:

Private Sub TextBox86_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    If TextBox86.Value = "" Then
    ElseIf Len(TextBox86) <> 6 Then
        MsgBox "Enter expiration date as mmddyy(eg. 021522)"
        Cancel = True
        If Cancel = True Then
        TextBox86.Value = ""
        Else
        End If
    End If
    Dim DateStr As String
        With Me.TextBox86
            Select Case Len(.Value)
            Case 6    ' e.g., 090298 = 2-Sep-1998
                DateStr = Left(.Value, 2) & "/" & _
                          Mid(.Value, 3, 2) & "/" & Right(.Value, 2)
            Case Else
                Exit Sub
            End Select
            .Value = DateStr
        If IsDate(TextBox86.Value) Then
        Else: MsgBox "Enter a valid date!"
        Cancel = True
        If Cancel = True Then
        TextBox86.Value = ""
        Else
        End If
        End If
        End With
    If IsDate(TextBox86.Value) Then
            If CDate(TextBox86.Value) < Date Then
            MsgBox "Saline Cube at Workstation 4 is Expired!", vbCritical
            If CDate(TextBox86.Value) < Date Then
            TextBox86.BackColor = &H8080FF
            End If
            If CDate(TextBox86.Value) >= Date Then
            TextBox86.BackColor = &HFFFFFF
            End If
    End If
    End If
    End Sub

I cannot figure out why I am encountering this error when these two codes are so similar.

Thanks for any help

 

[DanteAmor]

In this part change the value of the textbox to empty but you do not finish the process ...

If IsDate(TextBox86.Value) Then
Else: MsgBox "Enter a valid date!"
Cancel = True
If Cancel = True Then
TextBox86.Value = ""

So later, if the textbox87 is empty it generates the error.

If CDate(TextBox87.Value) < Date Then

I would structure the code like this:

Private Sub TextBox87_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  Dim DateStr As String
 
  With TextBox87
    If .Value = "" Then
      .BackColor = &HFFFFFF
      Exit Sub
    End If
  
    If Len(.Value) <> 6 Then
      MsgBox "Enter expiration date as mmddyy(eg. 021522)"
      Cancel = True
      .Value = ""
      Exit Sub
    End If
  
    DateStr = Left(.Value, 2) & "/" & Mid(.Value, 3, 2) & "/" & Right(.Value, 2)
    If Not IsDate(DateStr) Then
      MsgBox "Enter a valid date!"
      Cancel = True
      .Value = ""
      Exit Sub
    End If
  
    .Value = DateStr
  
    If IsDate(.Value) And ComboBox35.Text <> "*Bottle" Or ComboBox35.Text <> "*Saline" Then
      If CDate(.Value) < Date Then
        MsgBox "Saline " & ComboBox35.Text & " at Workstation " & ComboBox39.Text & " Expired!", vbCritical
        .BackColor = &H8080FF
      Else
        .BackColor = &HFFFFFF
      End If
    End If
  End With
End Sub

 

[pmgibs]

Thank you for the explanation and the re-structured code. This worked great!