VBA - run code on close of all workbooks (PERSONAL.xlsb code)

[mbarbera83]

Is it possible to create a code in the Personal.xlsb file that runs when ever you close excel? almost like the save message on close of excel?

I want to run a code that disables an addin everytime excel is closed.

please help

 

[baitmaster]

You could utilise the workbook_beforeClose event in the PMW. It's not quite the same thing, but assuming you have the PMW always open and not visible, it will be pretty much the same

Added to the Thisworkbook code module in the PMW:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
' your code here
End Sub

 

[mbarbera83]

You could utilise the workbook_beforeClose event in the PMW. It's not quite the same thing, but assuming you have the PMW always open and not visible, it will be pretty much the same

Added to the Thisworkbook code module in the PMW:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
' your code here
End Sub

Thanks , but sadly this does not work - for some reason it does not see that you are "closing" the PMW and therefore doesn't run the code.

 

[baitmaster]

Oh OK, didn't realise

You could instead make use of application-level events, but it's a bit more tricky

Brief overview at https://bettersolutions.com/vba/events/excel-application-level-events.htm

 

[mbarbera83]

Thanks. i have treid to have a look at that, but doesn't seem to work.
The problem is that the example talks about working on Book1, i need to be able to VBA on Personal as this is always open on excel start

This is all on Personal.xlsb
Option Explicit

Public WithEvents ExcelAppEvents As Application

Private Sub ExcelAppEvents_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
Result = MsgBox("Do you want to continue?", vbYesNo + vbQuestion)
If Result = vbYes Then
MsgBox "You clicked Yes"
Else: MsgBox "You clicked No"
End If
End Sub


This is in the Module1

Public ApplicationClass As New ApplicationEventClass
Sub ConnectEventHandler()
Set ApplicationClass.ExcelAppEvents = Application
End Sub

 

[baitmaster]

OK, here is an extract of what I use for similar things, adapted for you. I think it's complete

1) in the ThisWorkbook module

Private Sub Workbook_Open()
    ConnectEventHandler
End Sub

2) in a standard code module

Public ApplicationClass As New ApplicationEventClass

Sub ConnectEventHandler()
   Set ApplicationClass.ExcelAppEvents = Application
End Sub

3) in a class module named ApplicationEventClass

Private Sub ExcelAppEvents_WorkbookBeforeClose(ByVal wb As Workbook, Cancel As Boolean)
If wb.Name = thisworkbook.name Then MsgBox "PMW closing!"
End Sub

 

[mbarbera83]

I get a compile error on opening a workbook:
Method or data member not found
and it highlights the following statement:
Sub ConnectEventHandler()

also - no message box on closing

i have copied all the code as suggested in the PMW on:
the worksheet,
module1
Class module

 

[baitmaster]

Apologies, I missed this line from the top of the class module

Public WithEvents ExcelAppEvents As Application

Also, you mention "the worksheet", note that you need to be working with the ThisWorkbook module, not a worksheet module

 

[mbarbera83]

Baitmaster. i seem to have done everything as you have suggested, yet still nothing.
any advise?

I am trying to send you screenshots, but not sure how to on this forum

 

[baitmaster]

DM me an email address, I'll try to look at this over the weekend and drop a working file to you