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

mbarbera83

New Member
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

Well-known Member
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:
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
' your code here
End Sub
 
Last edited:

mbarbera83

New Member
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:
Code:
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.
 

mbarbera83

New Member
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

Well-known Member
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
Code:
Private Sub Workbook_Open()
    ConnectEventHandler
End Sub
2) in a standard code module
Code:
Public ApplicationClass As New ApplicationEventClass

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

3) in a class module named ApplicationEventClass
Code:
Private Sub ExcelAppEvents_WorkbookBeforeClose(ByVal wb As Workbook, Cancel As Boolean)
If wb.Name = thisworkbook.name Then MsgBox "PMW closing!"
End Sub
 
Last edited:

mbarbera83

New Member
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

Well-known Member
Apologies, I missed this line from the top of the class module
Code:
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

New Member
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
 

Some videos you may like

This Week's Hot Topics

Top