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

[Toppers]

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.


"Barb Reinhardt" wrote:

> 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
>
>
>
>

[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

--
Regards,
Tom Ogilvy


"Barb Reinhardt" <[email protected]> wrote in message
news:uih%[email protected]...
> 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
>
>
>

[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.
--
Regards,
Tom Ogilvy


"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Barb Reinhardt" <[email protected]> wrote in message
> news:uih%[email protected]...
> > 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
> >
> >
> >
>
>

[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."

--
Regards,
Tom Ogilvy



"tony h" <[email protected]> wrote in
message news:[email protected]...
>
> 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)
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile:
http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=503611
>

[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.


"Toppers" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>
> "Barb Reinhardt" wrote:
>
>> 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
>>
>>
>>
>>

[Tom Ogilvy]

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

--
Regards,
Tom Ogilvy


"Barb Reinhardt" <[email protected]> wrote in message
news:%[email protected]...
> 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.
>
>
> "Toppers" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
> >
> > "Barb Reinhardt" wrote:
> >
> >> 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
> >>
> >>
> >>
> >>
>
>