This is my first try at using event procedures. It's not going well.
I'm trying to run some code whenever a workbook with a certain name is
activated or deactivated. I'd like to keep the workbook free of macros
and keep the code in an add-in instead. If that's not possible,
disregard the rest of this and fetch me an ambulance.

The VBA help says to create a Class Module like this:

Public WithEvents App As Application

and then write event procedures there. And then "connect the declared
object in the class module with the Application object," as follows, by
running this code from any module:

Dim X As New EventClassModule

Sub InitializeApp()
Set X.App = Application
End Sub

This is where I get lost:

1. Where does the 'Dim' statement go? With the global decs?

2. Do I need to run InitializeApp manually at every Excel session?

I've tried various places for InitializeApp; still, neither of the
events I’m trying to recognize will fire. Currently it's in the
ThisWorkbook module of the add-in, near that module’s menu-building
code. If the answer to #2 above is Yes, should InitializeApp be called
from *within* the menu-building code, since that code will run each time
Excel opens?

As I say, neither event fires. Here's the Class Module (action code
simplified):

Option Explicit
Public WithEvents App As Application

Sub App_WorkbookActivate(ByVal Wb As Workbook)
If Wb.Name = "xyz.xls" Then MsgBox "Hi"
End Sub

Sub App_WorkbookDeactivate(ByVal Wb As Workbook)
If Wb.Name = "xyz.xls" Then MsgBox "Bye"
End Sub

What am I leaving out?!?! And assuming the answer is straightforward,
two more specific questions: Does the Workbook_Deactivate event fire
just *before* deactivation or after? (Specifically I'm wondering what
the value of Workbooks.Count is when that procedure begins, i.e., does
it include the workbook being deactivated.) And finally, if only one
workbook is open, does closing it also officialy "deactivate" it, for
purposes of this code?

Please help me understand this. Thanks very very much.

Mark Tangard
"Life is nothing if you're not obsessed." --John Waters

Perhaps Chip Pearson's explanation will be clearer:

http://www.cpearson.com/excel/AppEvent.htm

--
Regards,
Tom Ogilvy

"Mark Tangard" wrote in message
...
This is my first try at using event procedures. It's not going well.
I'm trying to run some code whenever a workbook with a certain name is
activated or deactivated. I'd like to keep the workbook free of macros
and keep the code in an add-in instead. If that's not possible,
disregard the rest of this and fetch me an ambulance.

The VBA help says to create a Class Module like this:

Public WithEvents App As Application

and then write event procedures there. And then "connect the declared
object in the class module with the Application object," as follows, by
running this code from any module:

Dim X As New EventClassModule

Sub InitializeApp()
Set X.App = Application
End Sub

This is where I get lost:

1. Where does the 'Dim' statement go? With the global decs?

2. Do I need to run InitializeApp manually at every Excel session?

I've tried various places for InitializeApp; still, neither of the
events I’m trying to recognize will fire. Currently it's in the
ThisWorkbook module of the add-in, near that module’s menu-building
code. If the answer to #2 above is Yes, should InitializeApp be called
from *within* the menu-building code, since that code will run each time
Excel opens?

As I say, neither event fires. Here's the Class Module (action code
simplified):

Option Explicit
Public WithEvents App As Application

Sub App_WorkbookActivate(ByVal Wb As Workbook)
If Wb.Name = "xyz.xls" Then MsgBox "Hi"
End Sub

Sub App_WorkbookDeactivate(ByVal Wb As Workbook)
If Wb.Name = "xyz.xls" Then MsgBox "Bye"
End Sub

What am I leaving out?!?! And assuming the answer is straightforward,
two more specific questions: Does the Workbook_Deactivate event fire
just *before* deactivation or after? (Specifically I'm wondering what
the value of Workbooks.Count is when that procedure begins, i.e., does
it include the workbook being deactivated.) And finally, if only one
workbook is open, does closing it also officialy "deactivate" it, for
purposes of this code?

Please help me understand this. Thanks very very much.

Mark Tangard
"Life is nothing if you're not obsessed." --John Waters

Thanks SO much. This was a lot more, um... penetrable than the help file.

Mark Tangard
"Life is nothing if you're not obsessed." --John Waters

Tom writes:
Perhaps Chip Pearson's explanation will be clearer:

http://www.cpearson.com/excel/AppEvent.htm

--
Regards, Tom Ogilvy

"Mark Tangard" wrote in message
...

This is my first try at using event procedures. It's not going well.
I'm trying to run some code whenever a workbook with a certain name is
activated or deactivated. I'd like to keep the workbook free of macros
and keep the code in an add-in instead. If that's not possible,
disregard the rest of this and fetch me an ambulance.

The VBA help says to create a Class Module like this:

Public WithEvents App As Application

and then write event procedures there. And then "connect the declared
object in the class module with the Application object," as follows, by
running this code from any module:

Dim X As New EventClassModule

Sub InitializeApp()
Set X.App = Application
End Sub

This is where I get lost:

1. Where does the 'Dim' statement go? With the global decs?

2. Do I need to run InitializeApp manually at every Excel session?

I've tried various places for InitializeApp; still, neither of the
events I’m trying to recognize will fire. Currently it's in the
ThisWorkbook module of the add-in, near that module’s menu-building
code. If the answer to #2 above is Yes, should InitializeApp be called
from *within* the menu-building code, since that code will run each time
Excel opens?

As I say, neither event fires. Here's the Class Module (action code
simplified):

Option Explicit
Public WithEvents App As Application

Sub App_WorkbookActivate(ByVal Wb As Workbook)
If Wb.Name = "xyz.xls" Then MsgBox "Hi"
End Sub

Sub App_WorkbookDeactivate(ByVal Wb As Workbook)
If Wb.Name = "xyz.xls" Then MsgBox "Bye"
End Sub

What am I leaving out?!?! And assuming the answer is straightforward,
two more specific questions: Does the Workbook_Deactivate event fire
just *before* deactivation or after? (Specifically I'm wondering what
the value of Workbooks.Count is when that procedure begins, i.e., does
it include the workbook being deactivated.) And finally, if only one
workbook is open, does closing it also officialy "deactivate" it, for
purposes of this code?

Please help me understand this. Thanks very very much.

Mark Tangard
"Life is nothing if you're not obsessed." --John Waters