Kevin,
Once I have created the Private Sub App_WorkbookOpen(ByVal Wb
As Workbook), how do I get it to run? Does it not run
automatically when the workbook is opened?
Yes, it will run automatically when any workbook is open or
created.
I am confused by the "Dim X as a New EvenClassModule" and the
Sub InitializeApp().
A class module is a template for an object. It doesn't by itself
allocate any memory or create an object. Only when you create an
instance of it (called "instantiating") will memory be allocated
an the object come into existence.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
"Kevin H. Stecyk" wrote in message
...
Hi,
I have never understood Class Modules very well. So please
bear with as I ask my questions.
My objective is to do the following:
1) Create a Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
2) In App_WorkbookOpen routine, I want to set a vba global
variable.
I am not well versed with using event handlers.
So XL 2003 Help says,
~~~~
Before you can use events with the Application object, you must
create a new class module and declare an object of type
Application with events. For example, assume that a new class
module is created and called EventClassModule. The new class
module contains the following code.
Public WithEvents App As Application
~~~~
I understand that.
Now it says,
~~~~
After the new object has been declared with events, it appears
in the Object drop-down list box in the class module, and you
can write event procedures for the new object. (When you select
the new object in the Object box, the valid events for that
object are listed in the Procedure drop-down list box.)
Before the procedures will run, however, you must connect the
declared object in the class module with the Application
object. You can do this with the following code from any
module.
Dim X As New EventClassModule
Sub InitializeApp()
Set X.App = Application
End Sub
After you run the InitializeApp procedure, the App object in
the class module points to the Microsoft Excel Application
object, and the event procedures in the class module will run
when the events occur.
~~~~
My understanding here is much weaker.
Once I have created the Private Sub App_WorkbookOpen(ByVal Wb
As Workbook), how do I get it to run? Does it not run
automatically when the workbook is opened? I am confused by
the "Dim X as a New EvenClassModule" and the Sub
InitializeApp(). If you understand where my confusion lies,
please clarify.
All I want to do at this point is set a global variable within
the Private Sub App_WorkbookOpen(ByVal Wb As Workbook) that can
be changed by a "normal" routine from within a normal (not
class) vba module. What do I need to do?
Thank you.
Kevin