Im creating a form that has a textbox. In that text i will ask to write a date. My problem is that i want to not allow the date 31-12-9999. How can it be done?

2 Answers

Tim Stack On Best Solutions

Code below will clear the textbox and show an error message in case that particular date was entered

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Me.TextBox1.Value = "31-12-9999" Then
        Me.TextBox1.Value = ""
        MsgBox "Date is not allowed", vbExclamation, "Error"
    End If
End Sub
Error 1004 On

This is another way to check string entered. Also this code make limit the possibilities of entering a wrong date.

Option Explicit

Private Sub UserForm_Click()

    Dim str As String
    Dim Counter As Long, i As Long

    With UserForm1

        str = .TextBox1.Value

        If str = "31-12-9999" Then
            MsgBox "Invalid date."
            Exit Sub
            'Test Lenght
            If Len(str) <> 10 Then
                MsgBox "Please check date's lenght."
                Exit Sub
            End If

            'Test "-" occurance
            If Len(str) - Len(Replace(str, "-", "")) <> 2 Then
                MsgBox "Please check date's separators."
                Exit Sub
            End If

            'Count Numeric values
            For i = 1 To Len(str)

                If IsNumeric(Mid(str, i, 1)) Then
                    Counter = Counter + 1
                End If

            Next i

            If Counter <> 8 Then
                MsgBox "Please check number of numberic values in the entered date."
                Exit Sub
            End If

            'Check date validation
            If Not IsDate(str) Then
                MsgBox "Please check date's day, month or year."
                Exit Sub
            End If

        End If

    End With

End Sub