How to initiate addin code on .BeforeClose() event

Question

I am trying to build my first excel addin that would initiate a code on close of the workbook once certain condition is met. The condition is checked by the on error go to end.

When I run my addin, it looks like this entire part of code does not get initiated.

This code is currently stored in the ThisWorkbook of the add-in.

Private WithEvents App As Application

Private Sub Workbook_Open()
    Set App = Application
End Sub

Private Sub App_BeforeClose(Cancel As Boolean)
    Dim wb1 As Workbook
    Dim lastrow As Integer

    On Error GoTo ENDING
    Application.ScreenUpdating = False
    Workbooks.Open (ActiveWorkbook.Sheets("ADDIN-

DONOTTOUCH").Cells(1.1).Value)
    Set wb1 = ActiveWorkbook
    Set ws = wb1.ActiveSheet
    lastrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

.....'Other code....

wb1.Save
wb1.Close

Application.DisplayAlerts = False
ActiveWorkbook.Sheets("ADDIN-DONOTTOUCH").Delete
Application.DisplayAlerts = True

Application.ScreenUpdating = True
ENDING:
End Sub

Answer 1

You shouldn't ever be typing event handler procedure signatures by hand.

Private WithEvents App As Application

This WithEvents variable adds an App entry in the code pane's left-hand dropdown:

Select it. Now expand the right-hand dropdown...

When you select a member in the RHS dropdown, the VBE automatically creates the handler procedure for you, and it is guaranteed to be valid and well-formed. Whereas if you type them from memory, a mistake in parameter ordering can cause a lot of grief.

In any case, the Excel.Application object does not expose a BeforeClose event, so while your procedure looks like an event handler, the LHS dropdown says (General) (instead of an event source object), and the "handler" is never actually invoked.

But, since ThisWorkbook is an add-in, it will be loaded along with Excel, and unless unloaded manually, will be unloaded also along with Excel - so if you handle Workbook.BeforeClose in ThisWorkbook, then you'll get to run custom code when Excel closes your add-in - whether because the add-in is being unloaded, or because Excel is shutting down.