Chris Mack
Well-known Member
- Joined
- Jun 18, 2013
- Messages
- 803
Hi all
I have the following code:
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
I have the following code:
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
It definitely passes the error handling line after each iteration of an InputBox, so what is the problem here?
Many thanks
Chris