Ending a macro with the [X] button

Nyanko

Active Member
Joined
Sep 1, 2005
Messages
437
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Forum statistics

Threads
1,191,163
Messages
5,985,028
Members
439,934
Latest member
Verdiana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top