And of course i ment Excel 2002 and not Word :-)

It seems that the xla only loads when removing+adding it in the tools/AddIn
menu, not when Excel loads... ?

Bent

"Bent Kjeldsen" wrote in message
...
Hi again.

Tried your example. The Workbook_Open() in ThisWorkbook, only fires when
the
workbook is installed in Tools/AddIns i Word (2002)

Then the BeforeSave is called... But when I close Excel, opens is, the
event
does not fire, and BeforeSave is never called. (Tried with msgbox)

Any ideas? And do make it work on a single workbook, do you have any
ideas?

Thanks a lot.

Bent

"Dag Johansen" wrote in message
...
Hi,

the problem is you are handling the event for the workbook
in which your xla resides. This is a separate workbook
from any workbooks the user has open and see in the window
menu. In fact the event will only fire when you open your
xla, enter the vba module, modify it and then save your
changes!

What you need to do is create a class module which
declares a member to reference an instance of the
application.

Dim WithEvents app As Excel.Application

Assign it in the handler of Class_Initialize:

Private Sub Class_Initialize()
set app = Application
End Sub

And declare an eventhandler to handle open and new events
on the workbook collection:

Private Sub app_NewWorkbook(ByVal Wb As Workbook)
'handle new
End Sub

Private Sub app_WorkbookOpen(..)
'handle open
End Sub

Private Sub app_WorkbookBeforeSave(..)

End Sub

Finally, a class module is one which can be instantiated,
i.e. several isolated "copies" of it may run concurrently,
with their private copies of the module variables. What it
means for your purpose is that you need to instantiate the
class module before you can handle any events. This you
can do in the WorkBook_Open event of your XLA, as the
event is fired when the plugin is loaded. Since you handle
events at the application level (for all open workbooks,
not a particular workbook) you only need one instance of
the class module.

In ThisWorkbook code:

Dim appEvents as <your class module's name

Private Sub Workbook_Open()
set appEvents = new <your class module's name
End Sub


Hope this isn't too confusing. Good luck!

Dag Johansen


-----Original Message-----
Hi.

I'm new to this, so if my question is trivial, i'm sorry.

When setting up the Workbook_BeforeSave() event in
ThisWorkBook in a xla,
and installing the xla in Excel, I thought this event was
fired for every
sheet ever saved in Excel.

Is it possible that this event is called for every opened
sheet that is
saved? (Can't depend on that the document is based on a
certain template or
so)

What am I missing?

Thanks

Bent


.