Help w/ Class to get app-wide Wkb.Open event?

[Ed]

I have a set of macros that I would like to run whenever any workbook
opens (XL2003). I looked in Help under the WorkbookOpen event, and it
seemed like what I wanted. Then I started getting bogged down in
creating a new Class module declaring an application object with
events??!?

I can copy code into a module with the best of them! But I'm having a
hard time understanding what I'm doing. I'm not familiar with Class
modules, and am not sure how all this works together.

From the Help file "Using Events with the Application Object":

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

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

Okay - so I create a new Class module and "Public WithEvents App As
Application" is ~all~ I put in it? Then in any macro I want to use
this I put

Dim X As New EventClassModule

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

at the top of the module? Then I can write events, such as
WorkbookOpen, for the object "X"?

From the Help file "WorkbookOpen Event":

**************************
Occurs when a workbook is opened.

Private Sub object_WorkbookOpen(ByVal Wb As Workbook)

object An object of type Application declared with events in a
class module. For more information, see Using Events with the
Application Object.

Wb The workbook.

Example
This example arranges all open windows when a workbook is opened.

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
Application.Windows.Arrange xlArrangeStyleTiled
End Sub
**************************

So if my new object is called "X", then this would actually look like
Private Sub X.WorkbookOpen(ByVal Wb As Workbook)
????

And somewhere I would have to set Wb = the workbook being opened to
pass into this?

If anyone can help my muddled brain make sense of this, I would
appreciate it.
Ed

 

[riso]

Maybe this help:

If you want to modify opening behaviour just for one workbook then
write this sub to the ThisWorkbook tab (VBA project/Microsoft Excel
Objects/ThisWorkbook - it is tab below Sheet1, Sheet2, ...)

Private Sub Workbook_Open()
call MacroThatCallsOtherMacros
End Sub

In the module just write

Sub MacroThatCallsOtherMacros
call macro1
call macro2
End Sub

and of course other macros

I hope this helps

riso

 

[Dave Peterson]

Chip Pearson has lots of good notes:
http://www.cpearson.com/excel/AppEvent.htm

Chip suggests using a class module, but there are other ways, too.

You can use this for a shell--it all goes into the ThisWorkbook module:

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub Workbook_Open()
Set xlApp = Application
End Sub
Private Sub Workbook_Close()
Set xlApp = Nothing
End Sub
Private Sub xlApp_NewWorkbook(ByVal Wb As Workbook)
MsgBox "Hey you created a workbook named: " & Wb.Name
End Sub
Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "Hey you opened a workbook named: " & Wb.Name
End Sub

=======
and I wouldn't use a variable named X (it's not too discriptive), but this:
Private Sub X.WorkbookOpen(ByVal Wb As Workbook)
would really look like:
Private Sub X_WorkbookOpen(ByVal Wb As Workbook)

 

[Doug Glancy]

Ed,

I think I can walk you through what you want.

Note, adding this code to a workbook will allow that workbook to monitor
application-level events. However, you will have to save and re-open that
workbook before it will do so, since the code to "hook" app-level events is
run in its Workbook_Open event. Also, I use "App" instead of "X" - I think
it's more descriptive. Here's what you do:

Create a new Class Module and name it "clsEventsApp". In that class module
insert the following code:

Public WithEvents App As Application
Private Sub App_WorkbookOpen(ByVal wb As Workbook)
MsgBox "Somewhere, a workbook has opened"
End Sub

In the ThisWorkbook module of the workbook, insert the following code:

Dim AppEvent As New clsEventsApp
Private Sub Workbook_Open()
Set AppEvent.App = Application
End Sub

Now, save and close the workbook. When you open it, you should get the
MsgBox message. And when you open any other workbook, you should also get
it. Note that creating a new workbook is a different event. To hook this
application event, go into your clsEventsApp module, click the dropdown on
the top left, choose "App", and in the top right dropdown, choose the first
choice, "NewWorkbook". This will create the App_NewWorkbook event. Add a
messagebox so you know it's working, like this:

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
MsgBox "Hey, it's a new workbook"
End Sub

In order to get the new message, save, close and reopen the workbook. Then
you'll get the NewWorkbook message every time you create one. Also note
that as long as this workbook is open it will monitor application-level
events, whether or not it's the active workbook.

hth,

Doug

 

[Ed]

Doug, this was great! Thanks so much!

Just because I don't understand, I still wonder about the
(ByVal wb As Workbook)

The code somehow automatically picks up the opened/new
workbook and assigns it to Wb? Then I can use Wb in the
code to do things to and with that workbook?

Ed

 

[Doug Glancy]

Ed,

Glad to help. Yes, you are exactly right about Wb. It's pretty cool stuff.

Doug