InputBox Date Validation

Question

I have an update form. When a button is clicked, a Yes or No message box pops up asking the user to verify the delivery date.
Upon clicking No, an InputBox appears to enter the appropriate date.

I am attempting to verify that the user entered a valid date. In the code this function begins at "Line1".

The problem occurs with the Else statement below "Line1".
If I enter a proper date, the code skips to the Msgbox and then returns me to "Line1".
If I enter a string of alpha characters, instead of the Message box prompting me to correct the date, I get

Runtime Error 13 Type Mismatch

Range("A" & Rows.Count).End(xlUp).Offset(1).Select
iRow = ActiveCell.Row
lastRow = ws3.Cells(ws3.Rows.Count, 1).End(xlUp).Row

For i = 3 To lastRow 'Isolate the Record, Get data, and move
    wo = Cells(i, 1).Value
    pn = Cells(i, 2).Value
    sn = Cells(i, 3).Value
    n = Cells(i, 6).Value
    If Me.txt_WN.Value = wo Then
        If Me.txt_pn.Value = pn Then
            If Me.txt_sn.Value = sn Then
                If n = "Yes" Then
                    dd = MsgBox("Is this the correct delivery date? " & Curr, vbYesNo)
                        If dd = vbYes Then
                            dd = Curr
                        Else
Line1: 'Problem exists here
                            If IsDate(dd = InputBox("Enter the Correct Delivery Date", "Deliver to Stores Date")) Then
                                dd = Format(dd, "mm/dd/yyyy")
                            Else
                                MsgBox "The date is formatted incorrectly, please recheck entry"
                                GoTo Line1
                            End If
                                'If IsDate(dd) Then
                                '        dd = Format(dd, "mm/dd/yyyy")
                                'Else
                                '    MsgBox "The date is formatted incorrectly, please correct"
                                '    GoTo Line1
                                'End If
                        End If
                    GoTo Update
                Else
                    MsgBox "This Wheel S/N was not marked as Due for NDT"
                    Exit Sub
                End If
            End If
        End If
    End If
Next i

Below the first If Statement after "Line1" a second if statement that has been commented out. In that instance, if I clicked the No button on the message box, it would enter "1/7/1900", and esentially bypass the code for validation (I think).

I used similar validation code in other subroutines and it worked.

Your IsDate() it not checking the date entered but the logical comparison of dd with the entered date which is either True or False. make it 2 lines, dd = Inputbox() then check with If isDate(dd). — CDP1802
– CDP1802, Commented Feb 26, 2023 at 16:30

CDP1802 · Accepted Answer · 2023-02-26 16:54:30Z

0

Avoid potential problems by qualifying the Cells() with a worksheet reference otherwise it defaults to the active sheet.

    With ws3
        lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
        For i = 3 To lastrow 'Isolate the Record, Get data, and move
            wo = .Cells(i, 1).Value
            pn = .Cells(i, 2).Value
            sn = .Cells(i, 3).Value
            n = .Cells(i, 6).Value
            If Me.txt_WN.Value = wo _
               And Me.txt_pn.Value = pn _
               And Me.txt_sn.Value = sn Then
                If n = "Yes" Then
                    If vbYes = MsgBox("Is this the correct Delivery Date? " & curr, vbYesNo) Then
                        dd = curr
                    Else
Line1:                  dd = InputBox("Enter the correct Delivery Date", "Deliver to Stores Date")
                        If Len(dd) = 0 Then ' cancel
                            Exit Sub
                        ElseIf IsDate(dd) Then
                            dd = Format(dd, "mm/dd/yyyy")
                        Else
                            MsgBox "'" & dd & "' is not a valid date, please recheck entry", vbExclamation
                            GoTo Line1
                        End If
                    End If
                    GoTo update
                Else
                    MsgBox "This Wheel S/N " & sn & " was not marked as due for NDT", vbCritical
                    Exit Sub
                End If
            End If
        Next i
    End With

answered Feb 26, 2023 at 16:54

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vincent Over a year ago

While your solution was not the final solution, it did get me thinking in the right direction, as well as illuminating some coding options that I didn't know about. Specifically the repeated use of 'And', as well as the 'lastrow' counting. I didn't know I could do that, so thank you for your excellent guidance.

Collectives™ on Stack Overflow

InputBox Date Validation

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related