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


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


.

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


"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


.

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


.

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


.

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)

"Bent Kjeldsen" wrote in 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

Sorry for mixing terms :-)

I figured it out. Thanks everybody.

Bent

"Bob Phillips" wrote in message
...
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)

"Bent Kjeldsen" wrote in 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