Programatically Skip Auto_Close Macro Code On Shutdown - Possible?

[thurberm]

Seems like it should be simple to do, but I've been searching and can't find the answer.

I have a workbook in which the Autpen code checks to see if the current computer date is after a date I've defined as a variable.

If this condition is true, I want the workbook to close immediately--do not pass go, nothing else. Just display my message box saying that "Your time is up" and shut Excel down after the user clicks OK.

Application.Quit does quit Excel, but it also allows execution of the full Auto_Close code. This I DON'T want it to do. There are a lot of saving, backing up, and maintenance tasks in the Auto_Close code that are unnecessary if the file is too old, it should just quit without fanfare.

How do I shut Excel down programmatically and skip the Auto_Close macro code?

 

[Domenic]

Try adopting the following solution. You'll notice that I declared two public variables: dtTarget is assigned the target date; and bExpired keeps track of whether time has expired. So these two variables are declared in a regular module...

Public dtTarget As Date
Public bExpired As Boolean

Then the following code is placed in the code module for ThisWorkbook...

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If bExpired Then Exit Sub
    'etc
    '
    '
End Sub

Private Sub Workbook_Open()
    dtTarget = Worksheets("Sheet1").Range("A1").Value
    If Date > dtTarget Then
        bExpired = True
        MsgBox "Your time is up.", vbInformation, "Expired"
        ThisWorkbook.Close SaveChanges:=False
    End If
End Sub

You'll notice that it closes the workbook, it doesn't quit the application. Otherwise, if you have other workbooks open, they too will be closed and you'll be prompted to save any changes.

Hope this helps!

 

[thurberm]

Thanks Domenic,

I'm sure that will work, but I went with a simpler solution. Can't believe I didn't see it before, it was staring me right in the face.

Since the end date of my calendar is fixed, a simple IF/GOTO statement in the beginning of the Auto_Close code did the trick.

That's what happens when I stare at code too long...

 

[Domenic]

That's great. I'm glad you found a simple solution.

Cheers!