Capture Workbook_Close Event for Any Open Workbook 3

[kjv1611]

Is it possible to capture the Workbook_Close event for any open workbook, or does the code have to be explicitly written on a per workbook basis?

For instance, I'd like to run some code after this manner, from the Personal.xls/personal.xlsb file:

Sub AnyWorkbookClose()
  'Run some checks on the closing workbook
End Sub

Is that possible at all? Or if I'm not clarifying the question enough, let me know.

Thanks.

 

[kjv1611]

Well... maybe I don't need to go that route.... I'm basically thinking about a redo of "AutoSave". One that would save files off of Temporary folders, such as if opened from email, things like that.

So, what I could do instead, I suppose, is run this every 5 minutes, for instance, and ONLY look at those files that have been changed.... so with that in mind..

Is there a way to check on each open workbook, and see whether or not it has any changes? If no changes, no need saving...

Thanks for any suggestions, references, etc.

 

[N1GHTEYES]

Look at the workbook.saved property. I think it gives you what you want.

Tony

 

[kjv1611]

Thanks. that part is now working great.

 

[Rofeu]

Don't know if you still need it, but you can trap application wide events. There's a good desciption in the Excel VBA Help:

You'd need to create a classmodule ("EventClassModule") and add:

Public WithEvents App As Application

And then add the following code to any regular module and for instance call InitializeApp in the workbook_open event of your personal.xls:

Dim X As New EventClassModule

Sub InitializeApp()
    Set X.App = Application
End Sub

Cheers,

Rofeu

 

[kjv1611]

Thanks, I'll look at that as well, Rofeu. Sounds very interesting indeed.