generic macro on close of excel workbook

[Anniebella]

Hi,
I need to run a macro whenever a spreadsheet in a partifcular folder is
closed & changes have been made to it. I have tried to put the macro in the
Personal.XLS but this is not being run when the updated spreadsheet is
closed.
What do I need to do, without having to define the macro in every spreadsheet.
THanks
Anne

[Dave Peterson]

You're going to either need to add code to each of the workbooks in that folder
or create a workbook that uses an application event.

Chip Pearson has lots of notes (and a sample workbook) at:
http://www.cpearson.com/excel/AppEvent.htm

Anniebella wrote:
>
> Hi,
> I need to run a macro whenever a spreadsheet in a partifcular folder is
> closed & changes have been made to it. I have tried to put the macro in the
> Personal.XLS but this is not being run when the updated spreadsheet is
> closed.
> What do I need to do, without having to define the macro in every spreadsheet.
> THanks
> Anne

--

Dave Peterson

[Patrick Molloy]

You can do this at the Application level using a class module.
Its rather easier than it sounds, so please take a moment and work through
the following example. It should take no more than 5 minutes.

The example shows how to create an XLA that one can use to run code whenever
a workbook is closed. It can easily be adapted for other events.

1) Open Excel and a new workbook
2) go to the IDE and add a CLASS MODULE. Change the name of the class module
to clXL
3) Add the following code to the class module's code page:

Option Explicit
Private WithEvents xl As Excel.Application
Private Sub Class_Initialize()
Set xl = Excel.Application
End Sub
Private Sub xl_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As
Boolean)
Cancel = (MsgBox("OK", vbYesNo, "Closing " & Wb.Name) = vbNo)
End Sub
Private Sub Class_Terminate()
Set xl = Nothing
End Sub

4) Add a standard code module
5) Add the following code to the standard code module:

Option Explicit
Public xl As clXL
Sub Auto_Open()
Set xl = New clXL
End Sub

6) Return to Excel, save the workbook as and XLA, eg XL_Class.XLA

7) close Excel

To test
8) Open Excel
9) Open XL_Class.xla you can do this from your recent file list, by
browsing with file open or use the add-in manager
10) add a new workbook. close it. you'll see the message as the application
level event is fired.

easy ? you bet!

Now, go back to the class module (clXL) and look at the code.
Select the sub called xl_WorkbookBeforeClose
Notice XL is in the oblect list. You wil also see that in the methods box,
WorkbookBeforeClose is showing. In this dropdown you will see all the
programmable methods that are available.

HTH
Patrick Molloy
Microsoft Excel MVP







"Anniebella" wrote:

> Hi,
> I need to run a macro whenever a spreadsheet in a partifcular folder is
> closed & changes have been made to it. I have tried to put the macro in the
> Personal.XLS but this is not being run when the updated spreadsheet is
> closed.
> What do I need to do, without having to define the macro in every spreadsheet.
> THanks
> Anne