Capture Response to EXCEL ALERTS [RESOLVED]

[ssei1]

When the user clicks the close "X" in the upper right hand corner of the excel application window... I get the alert.. "Do you want to save the changes made to XXX.XLS?" - I don't mind this but would like to capture the users response to "YES", "NO" or "CANCEL" so that I can direct my program to perform the proper actions. HOW DO I CAPTURE THE USER RESPONSE?

Thanks in advance,
Richard

[RobDog888]

For starters, totally do-able. I even added a way to disable the
right-click in any cell!

Step1: Write code in the "ThisWorkBook" module to declare a class to capture our events.
Step2: Write class to initialize and attach to Excel.
Step3: Write Close event and code to handle users response to the save question.
Step4: Enjoy!

'Name: 'ThisWorkBook' Object Module
Option Explicit
 
Private RD As clsMyEvents
 
Private Sub Workbook_Open()
    Set RD = New clsMyEvents
    MsgBox "My Events Created!", vbOKOnly + vbInformation, "My Excel Events"
End Sub
 
 
'Name: clsMyEvents
'Instancing: Private
Option Explicit
 
Public goXL As Excel.Application
 
Public WithEvents MyEvents As Excel.Workbook
 
Private Sub Class_Initialize()
    Set goXL = GetObject(, "Excel.Application")
    Set MyEvents = goXL.ActiveWorkbook
End Sub
 
Private Sub MyEvents_BeforeClose(Cancel As Boolean)
    Dim iResp As Integer
    iResp = MsgBox("RobDog888: Do you want to save your workbook?", vbYesNoCancel + vbQuestion, "My Excel Events")
    If iResp = vbYes Then
        goXL.ActiveWorkbook.Save
        If goXL.ActiveWorkbook.Saved = True Then
            MsgBox "Saved!", vbOKOnly + vbInformation, "My Excel Events"
        Else
            MsgBox "Error Saving Workbook!" & vbNewLine & "Sorry!", vbOKOnly + vbCritical, "My Excel Events"
        End If
    ElseIf iResp = vbCancel Then
        Cancel = True
    End If
End Sub
 
Private Sub MyEvents_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    MsgBox "No Right-Clicking!", vbOKOnly + vbInformation "My Excel Events"
    Cancel = True
End Sub

If you have any trouble I can attach the working Workbook example.

VB/Outlook Guru!

[ssei1]

GREAT SOLUTION - CAN'T THANK YOU ENOUGH !!!!!!!!!!

[RobDog888]

No prob.