The more I think about it, I prefer
(pseudo-code)
in a normal module
Public closingWorkbookName as String
in a class module
Sub App.Event_Workbook_Activate ()
Dim aWorkbook as Workbook
On Error Resume Next
Set aWorkBook = Workbooks(closingWorkbookName)
On Error Goto 0
If aWorkbook Is Nothing then
MsgBox closingWorkbookName & " has closed"
End If
closingWorkbookName = "MasterWorkbook":Rem name of workbook which holds this class module.
End Sub
If this code
Workbook_BeforeClose
closingWorkbookName = ThisWorkbook.Name
End Sub
is in the AppEvents code module, you will trigger for every workbook, or you could put the BeforeClose in ThisWorkbook code module for only those workbooks you wanted to trigger the alarm.
Bookmarks