Trigger macro when workbook closes?

[Paul Robinson]

Hi,
I have an addin which creates a menu of macros on the standard toolbar. One
macro saves the cell shading of a range on the Active WorkBook (to a public
array variable declared in the declarations section). If the active workbook
is closed (without closing the Addin because I don't necessarily close down
Excel), how can I reapply that shading to the range before the workbook
closes? I can't use WorkBook_BeforeClose can I, as that applies to
ThisWorkBook (the Addin)??
If WorkBook_BeforeClose applies to All workbooks, won't the macro try and
click in when the Addin closes too, even though shading is not applicable to
that workbook?
confused!
cheers
Paul

[Wilson]

AFAIK, the sub Workbook_BeforeClose applies to the workbook where the sub
resides
"Paul Robinson" <paul.r...@it-tallaght.ie> wrote in message
news:eSjKVLZ9AHA.1484@tkmsftngp03...

[Tom Ogilvy]

Have the addin instantiate Application Level events - then use the
application Level version of the event BeforeClose event which. This will
tell you which workbook is closing. You will need to have stored the
information on what workbook to operate on, so you can then compare the two
and see if action is appropriate.

Regards,
Tom Ogilvy

"Paul Robinson" <paul.r...@it-tallaght.ie> wrote in message
news:eSjKVLZ9AHA.1484@tkmsftngp03...

[Dave]

Paul,

You should can achieve this by using application level events. There is a
good description in help on how to do this- it involves creating a class
module and stuff...quite difficult to find, so I'll paste it....

<Extract from MS Excel 2000 Help>

Using Events with the Application Object

Before you can use events with the Application object, you must create a new
class module and declare an object of type Application with events. For
example, assume that a new class module is created and called
EventClassModule. The new class module contains the following code.

Public WithEvents App As Application
After the new object has been declared with events, it appears in the Object
drop-down list box in the class module, and you can write event procedures
for the new object. (When you select the new object in the Object box, the
valid events for that object are listed in the Procedure drop-down list
box.)

Before the procedures will run, however, you must connect the declared
object in the class module with the Application object. You can do this with
the following code from any module.

Dim X As New EventClassModule

Sub InitializeApp()
Set X.App = Application
End Sub
After you run the InitializeApp procedure, the App object in the class
module points to the Microsoft Excel Application object, and the event
procedures in the class module will run when the events occur.

<End Extract>

I've never tried doing this within an addin, so I'm not sure whether closing
the addin will trigger the App_WorkbookBeforeClose event. If it does, you
can easily check which workbook triggered the event and only apply the
formatting if it isn't the addin. E.g.

'this is the event procedure you'll end up with in the new class module
Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
If Wb.IsAddin then Exit Sub

'formatting code here for all other workbooks

End Sub

HTH,
Dave.

Paul Robinson <paul.r...@it-tallaght.ie> wrote in message
news:eSjKVLZ9AHA.1484@tkmsftngp03...

[VMac]

Hi,
Thanks for the replies.
I found the stuff on application level events in Green's book (p227) soon
after I mailed.

thanks again
Paul

[Tushar Mehta]

No. Workbook related events apply to the workbook they apply to! If
inside the 'ThisWorkbook' module, they apply to that workbook. However,
they can be used with any workbook object declared with 'withevents' or
to the application object (also declared with 'withevents'). For
examples of the latter see other responses in this discussion.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
--
In <ersCvnZ9AHA.2060@tkmsftngp07>, Wilson <jwi...@wickes.com> wrote