Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
generic macro on close of excel workbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
generic macro on close of excel workbook
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
generic macro on close of excel workbook
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Generic file path Excel Macro Question | Excel Discussion (Misc queries) | |||
run macro on workbook close | Excel Discussion (Misc queries) | |||
How to close a workbook which contains the macro that's just been run? | Excel Programming | |||
How to close a workbook which contains the macro that's just been run? | Excel Programming | |||
Close Workbook from Macro | Excel Programming |