VBA "On Error" Statement Not Being Recognised

[Chris Mack]

Hi all

I have the following code:

Private Sub Workbook_Open()
    Dim InitialMonth As String, Restarted As Boolean
    Restarted = False
    InitialMonth = Format(Date - 1, "mmmm yyyy")
    GoTo Start
Restart:
    Restarted = True
Start:
    If Restarted = True Then
        AlternateMonth = Application.InputBox("""" & AlternateMonth & """ is not a valid entry." & vbNewLine & vbNewLine & _
            "I am about to process " & InitialMonth & "." & vbNewLine & vbNewLine & _
            "To process a different month, please enter it here in the format ""mmm-yy"" (must be later than Apr-15) (otherwise, click OK to continue):")
    Else
        AlternateMonth = Application.InputBox("I am about to process " & InitialMonth & "." & vbNewLine & vbNewLine & _
            "To process a different month, please enter it here in the format ""mmm-yy"" (must be later than Apr-15) (otherwise, click OK to continue):")
    End If
    On Error GoTo Restart
    If AlternateMonth = False Then
        MsgBox "Cancelled"
'        ActiveWorkbook.Save
'        Application.Quit
    ElseIf AlternateMonth = "" Then
        FirstOfMonthToProcess = DateValue("1 " & InitialMonth)
    ElseIf DateValue("1-" & AlternateMonth) < DateValue("1-Apr-15") Then
        GoTo Restart:
    Else
        FirstOfMonthToProcess = DateValue("1-" & AlternateMonth)
    End If
'    Sheets("Trust").[A15] = MonthToProcess
End Sub

If I type in a random text string into the input box, the error handler works fine the first time around ("On Error GoTo Restart"). However, if I type the same string again, I get a run time error when VBA attempts to evaluate the line "ElseIf DateValue("1-" & AlternateMonth) < DateValue("1-Apr-15") Then".

It definitely passes the error handling line after each iteration of an InputBox, so what is the problem here?

Many thanks

Chris

 

[jasonb75]

I was also thinking of using IsDate instead of error handles, took me a while to get things in the right order though.

This also removed the need to step backwards in the code.

Private Sub Workbook_Open()
    Dim InitialMonth As String
    InitialMonth = Format(Date - 1, "mmmm yyyy")
    
    AlternateMonth = Application.InputBox("I am about to process " & InitialMonth & "." & vbNewLine & vbNewLine & _
        "To process a different month, please enter it here in the format ""mmm-yy"" (must be later than Apr-15) (otherwise, click OK to continue):")
    
    If AlternateMonth = False Then
        MsgBox "Cancelled"
'        ActiveWorkbook.Save
'        Application.Quit
    ElseIf AlternateMonth = "" Then
        FirstOfMonthToProcess = DateValue("1 " & InitialMonth)
    Else
        Do
            If IsDate(AlternateMonth) Then
                If DateValue("1-" & AlternateMonth) >= DateValue("1-Apr-15") Then
                    FirstOfMonthToProcess = DateValue("1-" & AlternateMonth)
                    Exit Do
                End If
            End If
            AlternateMonth = Application.InputBox("""" & AlternateMonth & """ is not a valid entry." & vbNewLine & vbNewLine & _
                "I am about to process " & InitialMonth & "." & vbNewLine & vbNewLine & _
                "To process a different month, please enter it here in the format ""mmm-yy"" (must be later than Apr-15) (otherwise, click OK to continue):")
        Loop
    End If
'    Sheets("Trust").[A15] = MonthToProcess
End Sub