Error Handling Question in VBA

[cob98tp]

Hi All,

I am writing a little menu-type operation that asks the user for a date. I have got code in place to test that what is entered in the Input box is actually a date and it all works ok. The problem is the second time the user enters a non-date, I can't seem to work out how to reset the error status - despite the Err.Clear command.

Can anybody tell me what I'm doing wrong?

Thanks again guys!

Public Sub RMOption3()

SelectDate:
    Dim a As Date
    On Error GoTo NotADate
    a = InputBox("What Date Do You Wish To Export The Data To? NOTE: Please Enter the Date in the Format dd/mm/yy", "Enter Date for Export.")
    
    If a > Date Then
        E1 = MsgBox("Date Cannot be in the Future. Please Try Again.", vbOKOnly, "Incorrect Date")
        Exit Sub
    End If
    
    If a >= Date - 62 Then
        E2 = MsgBox("Date Cannot be More Than 2 Months in the Past. Please Try Again.", vbOKOnly, "Incorrect Date")
        Exit Sub
    End If

    Dato = a
    
    Call RunMacro
    
    Exit Sub

NotADate:
    E3 = MsgBox("That is not a valid Date format, would you like to try again?", vbYesNo + 32, "Invalid Date")
    
    If E3 = vbYes Then
        Err.Clear
        GoTo SelectDate
    End If
    
    If E3 = vbNo Then
        E4 = MsgBox("Operation aborted.", 0 + 48, "Abort")
    End If

End Sub

 

[BrianB]

Hopefully this will help. It uses IsDate() function to check for a valid input in any format.

'==============================================================
'- MACRO TO GET DATE FROM USER IN ANY VALID FORMAT (Inputbox)
'- Keep showing inputbox until proper date entry or cancel
'- uses VBA IsDate() function to check
'- NB. If in a UserForm the 'Calendar Control' is better.
'- Brian Baulsom
'==============================================================
Sub test()
    Dim DateString As String
    Dim DateProper As Date
    Dim EntryOK As Boolean
    '----------------------------------------------------------
    '- keep showing inputbox until proper date or cancel
    EntryOK = False
    Do While EntryOK = False
        DateString = InputBox("Please enter date", "DATE ENTRY")
        '- Cancel button -> exit
        If DateString = "" Then Exit Sub
        '- check date
        If IsDate(DateString) Then
            EntryOK = True
        Else
            '- error message
            MsgBox ("That is not a valid date format." & vbCr _
                & "Please try again or Cancel.")
        End If
    Loop
    '------------------------------------------------------------
    ' change string to date
    DateProper = DateString
    '- format date as required
    DateString = Format(DateProper, "dd/mm/yyyy")
    MsgBox ("Date is " & DateString)
End Sub
'-------------------------------------------