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

mbarbera83

New Member
Joined
Feb 17, 2015
Messages
18
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,042
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:
Upvote 0

mbarbera83

New Member
Joined
Feb 17, 2015
Messages
18
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.
 
Upvote 0

mbarbera83

New Member
Joined
Feb 17, 2015
Messages
18
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
 
Upvote 0

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,042
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:
Upvote 0

mbarbera83

New Member
Joined
Feb 17, 2015
Messages
18
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
 
Upvote 0

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,042
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
 
Upvote 0

mbarbera83

New Member
Joined
Feb 17, 2015
Messages
18
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
 
Upvote 0

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,042
DM me an email address, I'll try to look at this over the weekend and drop a working file to you
 
Upvote 0

Forum statistics

Threads
1,186,327
Messages
5,957,229
Members
438,294
Latest member
Soikeo79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top