Excel Providing user message when RED X clicked & properly closing application.

[FrancisM]

Here is what I want to happen. When a user clicks on the Red X (Right corner), a message box pops up, “Click exit to close.”, The user clicks OK, & it disappears.

What is happening is, the message box pops up, OK is clicked & MS SAVE\NOT SAVE\CANCEL pops up. I have code in Thisworkbook & Module 1.

This code is located in Thisworkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Cancels The Red X on both the Application & the "Click....." window.

'If Not fMacro Then Cancel = True

'Reminds the user to click Exit button to close.

Msg = "Click Exit to close."

Ans = MsgBox(Msg, vbInformation, "Vocational Services Database - " & ActiveSheet.Name)

'Select Case Ans

'Case vbYes

Exit Sub

End Sub

I commented out code I have been trying.

This part of the code is in Module 1:

Option Explicit

Public fMacro As Boolean

Sub Exit_Referrals()

Dim Msg As String, Ans As Variant

Msg = "Would you like to Exit the Referral Workbook?"

Ans = MsgBox("Would you like to Exit the Referral Workbook?" & vbCr, vbYesNo, "Vocational Services Database - " & ActiveSheet.Name)

'ThisWorkbook.fMacro = True

Select Case Ans



Case vbNo

Sheets("TOC").Select

On Error Resume Next

Case vbYes

Sheets("TOC").Select

Application.Calculation = xlCalculationAutomatic

ThisWorkbook.Save

Application.Quit

Case Else

End Select

Exit Sub

End Sub

If I am on a sheet other than the table of Contents(TOC), & I click NO, it goes to the TOC. It should stay on the active sheet.

If I am on a sheet other than the table of Contents(TOC), & I click YES, the “Click exit to close.”, message box pops up. It should not. Once Ok is clicked the workbook & Excel closes. I have been working on the code so long, I feel like I am hitting a brick wall.

 

[FrancisM]

I solved half the problem. When the Red X is clicked a pop up message appears, & if ok is clicked the message disappears.
Here is the code located in This workbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Cancels The Red X on both the Application & the "Click....." window.
Dim Response As VbMsgBoxResult
Response = MsgBox("Please click the Exit button to close!", vbCritical, "Vocational Services Database - " & ActiveSheet.Name)
If Response = vbOK Then Cancel = True
End Sub

The only problem, I have now is when the Exit Button is clicked. the code runs as if it is going to close, & then the BeforeClose code runs again preventing closure.
Here is the code located in Module 1:

Option Explicit

Sub Exit_Referrals()
If MsgBox("Would you like to Exit the Referral Workbook?" & vbCr, vbYesNo, _
"Vocational Services Database - " & ActiveSheet.Name) = vbYes Then
Sheets("TOC").Select
Application.Calculation = xlCalculationAutomatic
ThisWorkbook.Save
Application.Quit
End If

End Sub

I know I am missing something, But I am not certain what.

 

[FrancisM]

Problem Solved. it required code in 2 locations, (Thisworkbook & Module 1).

Here is the code located in Thisworkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Cancels The Red X on both the Application & the "Click....." window.
Dim Response As VbMsgBoxResult
Application.Speech.Speak "Please click. the Exit button to close! ", SpeakAsync:=True
Response = MsgBox("Please click the Exit button to close!", vbCritical, "Vocational Services Database - " & ActiveSheet.Name)
If Response = vbOK Then Cancel = True
End Sub

This code is located in module 1:

Option Explicit
Sub Exit_Referrals()
Dim Msg As String, Ans As Variant
Msg = "Would you like to Exit the Referral Workbook?"
Ans = MsgBox("Would you like to Exit the Referral Workbook?" & vbCr, vbYesNo, "Vocational Services Database - " & ActiveSheet.Name)
Select Case Ans
Case vbNo
ActiveSheet.Select
On Error Resume Next
Case vbYes
ActiveSheet.Select
Application.Calculation = xlCalculationAutomatic
ThisWorkbook.Save
Application.Quit
Application.EnableEvents = False
End Select
End Sub

The combined code accomplishes the following: 1) Prevents closure by clicking the RED X, 2) allows user to remain on the current worksheet, or exit the workbook. If exit is chosen, both the workbook & excel closes.