I'm using the following code to control dates entered as a search procedure, using a calendar as a date entry. However I'd like the [X] button to exit the procedure, and can't figure out how ....
Code:
Public Sub GetValidDates(ByRef FromDate As Date, _
ByRef ToDate As Date, _
Optional MinDate As Date, _
Optional MaxDate As Date)
Dim bError As Boolean
Dim sErrorMessage As String
Dim vAbsenceStart As Variant, vAbsenceEnd As Variant
'----------------------------------------------------------------------------
' Code below taken with advice from
' http://www.mrexcel.com/forum/showthread.php?t=274520
'----------------------------------------------------------------------------
vAbsenceStart = ""
vAbsenceEnd = ""
Do
bError = False
sErrorMessage = ""
frmCalendarStart.Show
vAbsenceStart = CalendarVal
If IsDate(vAbsenceStart) = False Then
bError = True
vAbsenceStart = ""
sErrorMessage = "Start date not a date"
Else
If CheckDateInRange(Datex:=vAbsenceStart, _
MinDate:=MinDate, _
MaxDate:=MaxDate) = False Then
bError = True
sErrorMessage = "Month start Date is not in range"
vAbsenceStart = ""
End If
If bError = False Then
frmCalendarEnd.Show
vAbsenceEnd = CalendarVal
If IsDate(vAbsenceEnd) = False Then
bError = True
sErrorMessage = "End date is not a date"
vAbsenceEnd = ""
End If
End If
If bError = False Then
If CheckDateInRange(Datex:=vAbsenceEnd, _
MinDate:=MinDate, _
MaxDate:=MaxDate) = False Then
bError = True
sErrorMessage = "End Date is not in range"
vAbsenceEnd = ""
End If
End If
If vAbsenceEnd < vAbsenceStart Then
bError = True
sErrorMessage = "Start Date not before End date"
vAbsenceEnd = ""
End If
End If
If bError Then MsgBox Prompt:=sErrorMessage, Buttons:=vbOKOnly + vbCritical, Title:="Invalid Date!"
Loop While bError
FromDate = CDate(vAbsenceStart)
ToDate = CDate(vAbsenceEnd)
End Sub
Private Function CheckDateInRange(ByVal Datex As Variant, _
Optional MinDate As Date, _
Optional MaxDate As Date) As Boolean
'----------------------------------------------------------------------------
' Return False if specified date is not in range
'----------------------------------------------------------------------------
Dim datCur As Date
On Error GoTo labError
datCur = CDate(Datex)
If Not (IsMissing(MinDate)) Then
If datCur < MinDate Then
CheckDateInRange = False
Exit Function
End If
End If
If Not (IsMissing(MaxDate)) Then
If datCur > MaxDate Then
CheckDateInRange = False
Exit Function
End If
End If
CheckDateInRange = True
Exit Function
labError:
CheckDateInRange = False
End Function