Cancel on INPUTBOX macro causes error

[Barb Reinhardt]

I have the following snippet of code (thanks to someone here)

Public SummaryMin As Date
Public SummaryMax As Date
Public SummaryDate As Date

Do
SummaryDate = DateValue(Application.InputBox("Enter Summary Reporting
Date between " & SummaryMin & " and " & SummaryMax, "Reporting Date",
SummaryMin))
If Not Application.And(SummaryDate >= SummaryMin, SummaryDate <=
SummaryMax) Then
MsgBox "Invalid date. Please re-enter"
End If
Loop Until Application.And(SummaryDate >= SummaryMin, SummaryDate <=
SummaryMax)

It works fine unless I cancel. I get a RUNTIME error, type 13.


Thanks,
Barb Reinhardt

 

[Guest]

Barb,
As one of the previous respondents: you omitted the following
statement after the Inputbox line (which was in the earlier postings):

If SummaryDate = False then exit sub ' User cancelled

FYI: the code posted by JE McGimpsey is "better" than mine in that it
automatically checks for an invalid date e.g 32/1/06 as it set the TYPE
parameter to 1; you might want to refer and change to it.

 

[Tom Ogilvy]

Dim SummaryMin As Date
Dim SummaryMax As Date
Dim SummaryDate As Date
Dim sSummaryDate as String
Dim msg1 as String

msg1 = "Enter Summary Reporting Date between " & _
SummaryMin & " and " & SummaryMax
Do
sSummaryDate = MsgBox(msg1, _
"Reporting Date",SummaryMin)
if sSummaryDate = "" then Exit sub
SummaryDate = cDate(sSummaryDate)
If SummaryDate < SummaryMin or _
SummaryDate > SummaryMax Then
MsgBox "Invalid date. Please re-enter"
End If
Loop Until SummaryDate >= SummaryMin And _
SummaryDate <= SummaryMax

 

[Tom Ogilvy]

Whoops - used msgbox instead of inputbox -

Dim SummaryMin As Date
Dim SummaryMax As Date
Dim SummaryDate As Date
Dim sSummaryDate as String
Dim msg1 as String

msg1 = "Enter Summary Reporting Date between " & _
SummaryMin & " and " & SummaryMax
Do
sSummaryDate = InputBox(msg1, _
"Reporting Date",SummaryMin)
if sSummaryDate = "" then Exit sub
SummaryDate = cDate(sSummaryDate)
If SummaryDate < SummaryMin or _
SummaryDate > SummaryMax Then
MsgBox "Invalid date. Please re-enter"
End If
Loop Until SummaryDate >= SummaryMin And _
SummaryDate <= SummaryMax

If you want to validate that it is a date, then post back with how you want
to handle it.

 

[Barb Reinhardt]

Actually, I don't want to exit the sub if they've cancelled. I want it to
loop back and ensure that they've entered a date. Otherwise, other parts of
subsequent code will gack.

 

[tony h]

summaryDate is declared as a variant
if cancel is pressed a zero length string is returned. by testing for
this and setting summary date to an out-of-range value the code is kept
ok.

SummaryDate = DateValue(Application.InputBox("Enter Summary Reporting
Date between " & SummaryMin & " and " & SummaryMax, "Reporting Date",
SummaryMin))

if summarydate="" then summarydate=summarymin-1

If Not Application.And(SummaryDate >= SummaryMin, SummaryDate <=
SummaryMax) Then
MsgBox "Invalid date. Please re-enter"
End If
Loop Until Application.And(SummaryDate >= SummaryMin, SummaryDate <=
SummaryMax)

 

[Tom Ogilvy]

You are confusing the Excel Inputbox with the VBA inputbox. The vba
inputbox which is called with just

res = InputBox()

does return a zero length string when cancel is selected. However, Topper
is using the Excel InputBox called with

res = Application.InputBox()

According to help:

"If you click the Cancel button, InputBox returns False."

 

[Tom Ogilvy]

What happens if the user actually wants to cancel? It is bad form not to
allow the user a means to escape.