M
Mark Tangard
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
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