Cancel on INPUTBOX macro causes error
 

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

Cancel on INPUTBOX macro causes error
 

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

Cancel on INPUTBOX macro causes error
 

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" wrote in message
...
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

Cancel on INPUTBOX macro causes error
 

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" wrote in message
...
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" wrote in message
...
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

Cancel on INPUTBOX macro causes error
 

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" wrote in message
...
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

Cancel on INPUTBOX macro causes error
 

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

Cancel on INPUTBOX macro causes error
 

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" wrote in
message ...

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

Cancel on INPUTBOX macro causes error
 

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" wrote in message
...
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" wrote in message
...
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