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

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

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

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

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

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

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

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