PDA

View Full Version : Workbook_BeforeClose vs. Auto_Close



theta
12-03-2012, 03:43 AM
Hi all...I have been working on a project that originally started in Excel 97 > 2003. It is now running in 2010. Everything runs fine, except when closing I get an error message (runtime error) but the application has already got to the point where debugging is not an option.

On inspection, I found that the original code contains BOTH the BeforeClose and Auto_Close method.

Which method should be in use? I know they both behave slightly differently, one only working when the actual [x] is pressed.

Any ideas / help welcome :)

Workbook_BeforeClose

Sub Workbook_BeforeClose(Cancel As Boolean)

If Not (EXIT_TRIGGER) Then
' Prompt the user to save the project before continuing
If Not (Prompt_Save_Tax) Then
Exit Sub
End If

' Prompt the user to save the project before continuing
If Not (Prompt_Save_Eco) Then
Exit Sub
End If

' Prompt the user to save the project before continuing
If Not (Prompt_Save_Project) Then
Exit Sub
End If

' Prompt the user to save the project before continuing
If Not (Prompt_Save_Consol) Then
Exit Sub
End If

If MsgBox("Are you sure you want to close PROJECTX?", vbYesNo + vbExclamation + vbDefaultButton2, "PROJECTX") = 6 Then
On Error GoTo ErrorHandler
EXIT_TRIGGER = True
Application.EnableEvents = False
Application.DisplayAlerts = False
Close_All
Application.Quit
ThisWorkbook.Close
Application.EnableEvents = True
Else
Cancel = True
End If
End If

Exit Sub

ErrorHandler:
Application.Quit
End Sub


Auto_Close

Sub Auto_Close()
On Error GoTo Error_Handler

If Not (EXIT_TRIGGER) Then

' Prompt the user to save the project before continuing
If Not (Prompt_Save_Tax) Then
Exit Sub
End If

' Prompt the user to save the project before continuing
If Not (Prompt_Save_Eco) Then
Exit Sub
End If

' Prompt the user to save the project before continuing
If Not (Prompt_Save_Project) Then
Exit Sub
End If

' Prompt the user to save the project before continuing
If Not (Prompt_Save_Consol) Then
Exit Sub
End If

If MsgBox(Translate("Are you sure you want to close PROJECTX?"), vbYesNo + vbExclamation + vbDefaultButton2, "PROJECTX") = 6 Then
EXIT_TRIGGER = True
If ProdComponent.Count > 0 Then
If Not (Close_All) Then Exit Sub
End If

ThisWorkbook.Close

End If
End If

'If Workbooks.Count > 0 Then Cancel = True Else ThisWorkbook.Close

Exit Sub

Error_Handler:
Application.Quit
End Sub

Aflatoon
12-04-2012, 01:50 AM
They ought to both work the same, except where manipulating the file in code is concerned, and you should use one or the other but not both. Which one to use is a matter of personal preference generally-some prefer to avoid code in ThisWorkbook whenever possible.