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

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

Hi Chip,

Thank you very much for responding to my question. As I mentioned, I don't
understand class modules very well.

From your response, I get the impression that I need to ""Dim X as a New
EvenClassModule" and the Sub InitializeApp()". I am testing that statement
to see whether you agree or not. If I don't do that, then what happens?
For example, let's say I don't "instantiate" the class module, will "Create
a Private Sub App_WorkbookOpen(ByVal Wb As Workbook)" still function? Can I
still set my global variable?

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

This goes in the normal (non-class) module, correct? What calls "Sub
InitializeApp()"?

I am not sure if my questions are making sense or not.

Thank you for patience and your help.

Best regards,
Kevin






Chip Pearson wrote...
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...
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

"Kevin H. Stecyk" wrote in message
...

From your response, I get the impression that I need to ""Dim X as a New
EvenClassModule" and the Sub InitializeApp()". I am testing that
statement
to see whether you agree or not. If I don't do that, then what happens?

Without this, nothing happens, because as Chip says, all you have is an
object template, you will not have created an instance of that oject.

For example, let's say I don't "instantiate" the class module, will
"Create
a Private Sub App_WorkbookOpen(ByVal Wb As Workbook)" still function? Can
I
still set my global variable?

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

If you don't Dim X, this statement will error.


This goes in the normal (non-class) module, correct? What calls "Sub
InitializeApp()"?

I tend to do it in a normal workbook_Open event for that workbook. This is
my example post on App Events.


Firstly, all of this code goes in the designated workbook.



'========================================Insert a class module, rename it to
'clsAppEvents', with this codeOption Explicit


Public WithEvents App As Application


Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

msgbox Wb.Name

End Sub


'========================================In ThisWorkbook code module, add
this event codeDim AppClass As New clsAppEvents


Private Sub Workbook_Open()


Set AppClass.App = Application


End Sub



Either save the workbook, close it and re-open it to initaite application
events, or just run the Workbook_Open code manually. From then on, each
workbook opened will display the name.


--

Hi Bob,

Sorry to be such a bother. I tried following your instructions explicitly.
However, I have done something wrong. I will copy and paste directly from
my code.

Class Module: clsAppEvents (copied and pasted)

Option Explicit

Public WithEvents App As Application


Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

MsgBox Wb.Name

End Sub

--------------

Now the Module1 is simply:

Option Explicit

Dim AppClass As New clsAppEvents



Private Sub Workbook_Open()


Set AppClass.App = Application


End Sub


When I close and reopen, I get nothing. When I single step through Private
Sub Workbook_Open(), I get nothing. No errors or anything.

Can you see where I went wrong?

Best regards,
Kevin

Try putting:

Option Explicit
Dim AppClass As New clsAppEvents
Private Sub Workbook_Open()
Set AppClass.App = Application
End Sub

Not in a general module (module1), but in the ThisWorkbook Module.

If you want to read more about application events, visit Chip Pearson's site:
http://www.cpearson.com/excel/AppEvent.htm

Chip also has a sample workbook there that you can download and test out.

"Kevin H. Stecyk" wrote:

Hi Bob,

Sorry to be such a bother. I tried following your instructions explicitly.
However, I have done something wrong. I will copy and paste directly from
my code.

Class Module: clsAppEvents (copied and pasted)

Option Explicit

Public WithEvents App As Application

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

MsgBox Wb.Name

End Sub

--------------

Now the Module1 is simply:

Option Explicit

Dim AppClass As New clsAppEvents

Private Sub Workbook_Open()

Set AppClass.App = Application

End Sub

When I close and reopen, I get nothing. When I single step through Private
Sub Workbook_Open(), I get nothing. No errors or anything.

Can you see where I went wrong?

Best regards,
Kevin

--

Dave Peterson

Dave, Bob, and Chip,

Thank you Dave for spotting my error! Thank you everyone for your patient
assistance.

I am going to have a look at Chip's information.

Best regards,
Kevin





Dave Peterson...
Try putting:

Option Explicit
Dim AppClass As New clsAppEvents
Private Sub Workbook_Open()
Set AppClass.App = Application
End Sub

Not in a general module (module1), but in the ThisWorkbook Module.

If you want to read more about application events, visit Chip Pearson's
site:
http://www.cpearson.com/excel/AppEvent.htm

Chip also has a sample workbook there that you can download and test out.

Dave Peterson wrote
Try putting:

Option Explicit
Dim AppClass As New clsAppEvents
Private Sub Workbook_Open()
Set AppClass.App = Application
End Sub

Not in a general module (module1), but in the ThisWorkbook Module.

If you want to read more about application events, visit Chip Pearson's
site:
http://www.cpearson.com/excel/AppEvent.htm

Chip also has a sample workbook there that you can download and test out.


Dave,

Just a follow up question to assist with my understanding.

In the ThisWorkbook Module, we placed a Work_Open() event handler which
instantiated the Class Module called clsAppEvents. In the clsAppEvents, we
have another workbookopen event handler called App_WorkbookOpen(ByVal Wb As
Workbook). My question is, why not just use the Work_Open() event handler
in the ThisWorkbook Module? Why use two event handlers that act upon the
workbook being opened?

I did read Chip's site that you referenced. That was helpful. I am going
to soak on it over night and then follow up with some questions tomorrow.

Again, thank you for your patience and assistance.

Best regards,
Kevin

What Chip doesn't mention and what I find a lot easier...

you dont need a separate class module..
Thisworkbook IS a class module so you can keep all your code
(and the withevents application variable) there.

'thisworkbook code module...
Option Explicit

Private WithEvents XlsEvents As Application

Private Sub Workbook_Open()
Set XlsEvents = Application
End Sub

Private Sub XlsEvents_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "You just opened " & Wb.Name
End Sub




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Dave Peterson wrote :

Try putting:

Option Explicit
Dim AppClass As New clsAppEvents
Private Sub Workbook_Open()
Set AppClass.App = Application
End Sub

Not in a general module (module1), but in the ThisWorkbook Module.

If you want to read more about application events, visit Chip
Pearson's site: http://www.cpearson.com/excel/AppEvent.htm

Chip also has a sample workbook there that you can download and test
out.

"Kevin H. Stecyk" wrote:

Hi Bob,

Sorry to be such a bother. I tried following your instructions
explicitly. However, I have done something wrong. I will copy and
paste directly from my code.

Class Module: clsAppEvents (copied and pasted)

Option Explicit

Public WithEvents App As Application

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

MsgBox Wb.Name

End Sub

--------------

Now the Module1 is simply:

Option Explicit

Dim AppClass As New clsAppEvents

Private Sub Workbook_Open()

Set AppClass.App = Application

End Sub

When I close and reopen, I get nothing. When I single step through
Private Sub Workbook_Open(), I get nothing. No errors or anything.

Can you see where I went wrong?

Best regards,
Kevin

If you want to set the variable for only a single workbook, then you don't
need application level events - just use the workbook_Open event as you
stated.

--
Regards,
Tom Ogilvy


"Kevin H. Stecyk" wrote in message
...
Dave Peterson wrote
Try putting:

Option Explicit
Dim AppClass As New clsAppEvents
Private Sub Workbook_Open()
Set AppClass.App = Application
End Sub

Not in a general module (module1), but in the ThisWorkbook Module.

If you want to read more about application events, visit Chip Pearson's
site:
http://www.cpearson.com/excel/AppEvent.htm

Chip also has a sample workbook there that you can download and test
out.


Dave,

Just a follow up question to assist with my understanding.

In the ThisWorkbook Module, we placed a Work_Open() event handler which
instantiated the Class Module called clsAppEvents. In the clsAppEvents,
we
have another workbookopen event handler called App_WorkbookOpen(ByVal Wb
As
Workbook). My question is, why not just use the Work_Open() event handler
in the ThisWorkbook Module? Why use two event handlers that act upon the
workbook being opened?

I did read Chip's site that you referenced. That was helpful. I am going
to soak on it over night and then follow up with some questions tomorrow.

Again, thank you for your patience and assistance.

Best regards,
Kevin

Hi Tom,

Thank you for answering my question. That's interesting. So you only need
to use the class modules for event handlers when you are working with
external workbooks. That helps to reinforce Chip's notes.

Again, thank you.

Best regards,
Kevin


Tom Ogilvy wrote...
If you want to set the variable for only a single workbook, then you don't
need application level events - just use the workbook_Open event as you
stated.

--
Regards,
Tom Ogilvy

ThisWorkbook is a class module, but it is already set up for you. You only
need explicitly instantiate application level events if you want to have
events that are independent of the activities associated with an individual
workbook or workhseet. In other words if I wanted to do something for any
workbook that is opened and I don't want to put the code in each workbook
then I would use application level events.

The common way show to instantiate application level events is with a class
module, but KeepItCool pointed out that this could be done within the
thisworkbook module of workbook. Even if using a separate class module, it
would still have to reside in a single workbook.

so your statement:

So you only need
to use the class modules for event handlers when you are working with
external workbooks.

Is accurate.

--
Regards,
Tom Ogilvy




"Kevin H. Stecyk" wrote in message
...
Hi Tom,

Thank you for answering my question. That's interesting. So you only
need
to use the class modules for event handlers when you are working with
external workbooks. That helps to reinforce Chip's notes.

Again, thank you.

Best regards,
Kevin


Tom Ogilvy wrote...
If you want to set the variable for only a single workbook, then you
don't
need application level events - just use the workbook_Open event as you
stated.

--
Regards,
Tom Ogilvy

Hi Tom,

Let me put this in my own words to see if I have completely understood.

1) You need class modules for event handlers.

2) ThisWorkbook is a class module. It is "already set up for you" in the
sense that you don't need to instantiate it. That's already set up. The
Workbook_Open event, for example, is already instantiated. (Question, are
any and all event handlers placed in ThisWorkbook class module
pre-instantiated? Could you place ALL your event handler for the current
and external workbooks in ThisWorkbook class module?)

3) ThisWorkbook class module is used for current workbook events.
(Question, can you place other event handlers in ThisWorkbook class module
that are used for external workbooks? I am guessing NO. But I want to be
clear in my understanding.)

4) You can create your own class modules. When dealing with external
workbooks, event handlers are usually created in a separate class modules
outside of ThisWorkbook class module. You can rename your class modules to
suit your purpose. But these class modules remain void until they are
instantiated by another event handler within ThisWorkbook class module.
Once they are instantiated, they become active.

Thank you Tom for walking me through this material.

Best regards,
Kevin

=================

Written by Tom Ogilvy....


ThisWorkbook is a class module, but it is already set up for you. You only
need explicitly instantiate application level events if you want to have
events that are independent of the activities associated with an individual
workbook or workhseet. In other words if I wanted to do something for any
workbook that is opened and I don't want to put the code in each workbook
then I would use application level events.

The common way show to instantiate application level events is with a class
module, but KeepItCool pointed out that this could be done within the
thisworkbook module of workbook. Even if using a separate class module, it
would still have to reside in a single workbook.

so your statement:

So you only need
to use the class modules for event handlers when you are working with
external workbooks.

Is accurate.

--
Regards,
Tom Ogilvy

See my answer to your later posting as new thread.

--
Regards,
Tom Ogilvy

"Kevin H. Stecyk" wrote in message
...
Hi Tom,

Let me put this in my own words to see if I have completely understood.

1) You need class modules for event handlers.

2) ThisWorkbook is a class module. It is "already set up for you" in the
sense that you don't need to instantiate it. That's already set up. The
Workbook_Open event, for example, is already instantiated. (Question, are
any and all event handlers placed in ThisWorkbook class module
pre-instantiated? Could you place ALL your event handler for the current
and external workbooks in ThisWorkbook class module?)

3) ThisWorkbook class module is used for current workbook events.
(Question, can you place other event handlers in ThisWorkbook class module
that are used for external workbooks? I am guessing NO. But I want to be
clear in my understanding.)

4) You can create your own class modules. When dealing with external
workbooks, event handlers are usually created in a separate class modules
outside of ThisWorkbook class module. You can rename your class modules
to
suit your purpose. But these class modules remain void until they are
instantiated by another event handler within ThisWorkbook class module.
Once they are instantiated, they become active.

Thank you Tom for walking me through this material.

Best regards,
Kevin

=================

Written by Tom Ogilvy....


ThisWorkbook is a class module, but it is already set up for you. You
only
need explicitly instantiate application level events if you want to have
events that are independent of the activities associated with an
individual
workbook or workhseet. In other words if I wanted to do something for any
workbook that is opened and I don't want to put the code in each workbook
then I would use application level events.

The common way show to instantiate application level events is with a
class
module, but KeepItCool pointed out that this could be done within the
thisworkbook module of workbook. Even if using a separate class module,
it
would still have to reside in a single workbook.

so your statement:

So you only need
to use the class modules for event handlers when you are working with
external workbooks.

Is accurate.

--
Regards,
Tom Ogilvy