Workbook_BeforeSave() in xla

  • Thread starter Thread starter Bent Kjeldsen
  • Start date Start date
B

Bent Kjeldsen

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
 
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
 
Hey Thanks!

Actually, the event should only fire for some workbooks. I just thought I
had to do it on every woorkbook, and then make some "if-then".

I open the workbook from VBScript in an aspx page, and are able to fire
commands to my Application, for these specific workbooks. How can I do it,
so the events only fires for these workbooks and not other?

Thanks a lot.

Bent
 
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
 
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,

This event is fired when the workbook is saved.

What makes you think a sheet is saved? What are you seeing?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top