Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2013
    Posts
    6

    Unanswered: Check value of textbox & return to userform if incorrect

    I have a large if statement within my code that checks the value of a textbox to make sure the date is in the format that I want. The only issue is that when it fails one of the elseif statements it immediately jumps out of the userform. Whereas I want it to go back to the userform & allow the user to edit it. I have a feeling it might have to do with my large amount of Ends & Nexts but i'm having a hard time sorting through them.

    Any suggestions?




    Private Sub btnDone_Click()
    num = txtDate.Value

    If txtDate.TextLength <> 10 Then
    MsgBox "Please enter the date as follows 'MM/DD/YYYY'"
    txtDate.SetFocus

    ElseIf IsNumeric(Mid(txtDate.Value, 6, 1)) = True Then
    MsgBox "Please enter the date as follows 'MM/DD/YYYY'"

    ElseIf IsNumeric(Mid(txtDate.Value, 3, 1)) = True Then
    MsgBox "Please enter the date as follows 'MM/DD/YYYY'"

    ElseIf IsNumeric(Mid(txtDate.Value, 1, 2)) = False Then
    MsgBox "Please enter the date as follows 'MM/DD/YYYY'"

    ElseIf IsNumeric(Mid(txtDate.Value, 4, 2)) = False Then
    MsgBox "Please enter the date as follows 'MM/DD/YYYY'"

    ElseIf IsNumeric(Mid(txtDate.Value, 7, 4)) = False Then
    MsgBox "Please enter the date as follows 'MM/DD/YYYY'"

    ElseIf Mid(txtDate.Value, 3, 1) <> "/" Then
    MsgBox "Please enter the date as follows 'MM/DD/YYYY'"

    ElseIf Mid(txtDate.Value, 6, 1) <> "/" Then
    MsgBox "Please enter the date as follows 'MM/DD/YYYY'"

    Else

    For Each c In Worksheets("Foreman").Range("A4:A237").Cells
    If c.Value = comp Then

    For d = 1 To 10 Step 1
    If c.Offset(0, d) = "" Then
    c.Offset(0, d).Value = num
    Unload Me
    MsgBox "Would you like to enter in more dates?", vbYesNo
    response = MsgBox("Would you like to enter in more dates?", vbYesNo)

    If response = vbYes Then
    usfmComp.Show
    Else
    End
    End If


    End

    Else
    End If
    Next d
    ' Do

    End If
    Next c

    End If

    End

    End Sub

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Maybe try something different to have fewer IF tests.
    Code:
    Private Sub CommandButton2_Click()
    Dim testDate As String
    Dim validDate As Date
    
        If Len(Trim(TextBox1.Value)) = 0 Then
            TextBox1.SetFocus
            Exit Sub
        End If
        
        On Error Resume Next
        testDate = Str(CDate(TextBox1.Text))
        On Error GoTo 0
        
        If testDate = "" Then
            MsgBox "Date format must be MM/DD/YYYY and must be a valid date"
            TextBox1.SetFocus
            Exit Sub
        Else
            validDate = CDate(testDate)
        End If
        
        'date is good--continue with the code
        
        MsgBox "You entered a valid date: " & validDate
        
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •