Hi Peter,

An Excel add-in (XLA) does not have an equivalent event to the
OnDisconnection or OnBeginShutdown events for a COM Add-in.

As such I believe that the best solution in this situation would be to add
your own custom code to the WorkBook_BeforeClose event that would check for
any workbooks that haven't been saved, and then display a custom message
box that would contain the Yes, No, and Cancel options as the dialog
displayed by Excel.

This would allow the code in your add-in to handle a scenario where the
user would click Cancel so that the menus would not be removed.

I have included sample code below to demonstrate what this code might look
like. Please note that the code below is not complete and you would need
to customize this for your add-in.

Sample Code
=====================
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim wbk As Workbook


For Each wbk In Application.Workbooks

If wbk.Saved = False Then

CloseWorkbooks wbk, Cancel

End If

Next

End Sub


Sub CloseWorkbooks(wbk As Workbook, ByRef Cancel As Boolean)

Select Case MsgBox("Do you want to save the changes you made to '" &
wbk.Name & "'?", _
vbExclamation + vbYesNoCancel + vbDefaultButton1, "Microsoft Excel")

Case vbYes

If wbk.Path < "" Then

wbk.Save

Else

wbk.Activate

If Application.Dialogs(xlDialogSaveAs).Show = False Then

CloseWorkbooks wbk, Cancel

Else

Exit Sub

End If

End If

Case vbNo

wbk.Saved = True

Case vbCancel

Cancel = True

Exit Sub

End Select

End Sub
====================================

I hope this helps!

If you have any questions please let me know via the posting.

Regards,

Ken Laws
Microsoft Support


This posting is provided "AS IS" with no warranties, and confers no rights.