With class modules, you can create your own objects. You can also extend the function of existing objects. In this post, I will describe how to use a class module to hook events of the Application object.
With objects such as the Workbook object or the Worksheet object, Excel has provided a class module for you (e.g. ThisWorkbook, Sheet1) complete with events for you to use. It doesn’t, however, provide a class module for the Application object. For that, you need to create your own class module.
Why would you want to? The Worksheet object has a Change event that fires whenever a cell on that worksheet is changed. The Workbook object has a SheetChange event that fires whenever a cell on any sheet in that Workbook is changed. Application events go one step further. For instance, the Application object has a SheetChange event that fires whenever a cell on any sheet in any workbook is changed – application wide. There are other useful events for the Application object that can’t be found anywhere else. Let’s get started setting one up.
First, you’ll need a blank workbook with a standard module and a class module. In the standard module, type one line like this
Public gxlApp As Class1
A public variable in a standard module will never go out of scope as long as the workbook is open. We need our “App” variable to be around all the time or the events won’t fire. Class1 is the name of our class module. In the class module, create another variable like this
Public WithEvents xlApp As Application
The WithEvents keyword tells VBA that we want access to this objects events, the object in question being the Application object. With just this much done, you can see which events are available by using the dropdowns at the top of the code pane.
Now let’s create an event procedure. In the class module, use the dropdowns to insert the Workbook_NewSheet event. This will insert the Sub and End Sub statements between which you can type your code. Create a macro that defines a certain header whenever a worksheet is inserted into any workbook.
Private Sub xlApp_WorkbookNewSheet(ByVal Wb As Workbook, ByVal Sh As Object)
If TypeName(Sh) = "Worksheet" Then
Sh.PageSetup.CenterHeader = "ABC, Inc."
End If
End Sub
Any time a sheet is added to any workbook, this event will fire. If the sheet is a worksheet, the header will be added. There’s one more step before it will work. We need to tell VBA that our public variable gxlApp represents the Application object. This is commonly done in the Workbook_Open event. In the ThisWorkbook module, type a procedure like this
Private Sub Workbook_Open()
Set gxlApp = New Class1
Set gxlApp.xlApp = Application
End Sub
Press F5 while in this procedure to simulate the workbook opening. Now add a sheet to the workbook and watch the magic. For even more fun, add a new workbook and add a sheet to that.
This example is only illustrative. There are better ways to add a header to new worksheets. The basic steps to using application level events are
- Create a public variable in a standard module
- Create a class module with a public variable using the WithEvents keyword and dimmed as the Application object.
- Assign the class module variable to the object in a procedure (usually Workbook_Open)
I got this to work, only, by making two modifications:
Changing the first line of code from
‘Public gxlApp As Class1’ to
‘Dim gxlApp As New Class1’ and
by putting it in ‘ThisWorkbook’ instead of
in a standard Module.
All of the other code and code storage locations are identical.
I conjured these changes by: looking at two other samples; and trial and error. Definitely not from my limited knowledge of VBA.
Now, my changes work when the code is stored in an .xlS file; however, my changes would not work when stored in an .xlA file. (Note, this .xlA file is not installed as an ‘AddIn’ in Excel.)
Any comments?
Chuckles123
Dick,
This is extremely useful to me — I just used a variation on your code to add a useful function to my “toolbox” addin… I can use the keyboard to jump back to the last sheet I was on, and also to the last workbook. I was able to jump between sheets before only by adding code to each workbook in which I wanted to do it, but this is far simpler. Thanks!
it was interesting to read the article.
do you have more about class modules, like is the number or class modules per workbook is restricted
how do i set differenct class modules for openning
a workbook and “before closing” a workbook,
where the class modules are in “personal”
do you have articles of how to create user objects,
like for database in excel, and or database in excel
to be inserted into sql sever database
sorry for asking too mant questions and thanks for
your attention
rachel
Rachel: I have plans to post more about class modules. They take longer than the average post, so it’s easy to procrastinate. It’s nice to know what you’re looking for so I can tailor the post – and at least one person will find it useful.
Hi Rachel,
our computer crashed and I lost all of my e-mail and skype addresses. Could you please send me both of your addresses.
Arlene
What about an event to deal with the KeyPress event, if a “smart” user tries to delete a cell in a protected worksheet for instance? I know that excel fires up a protected workbook/sheet dialoge but I would like to capture the event in a protected access database that my excel workbook uses for information for the various forms etc., I have done a Google search but no luck…
Any ideas or do I need to think of perhaps another method?
Mark
Hi!
I tried this tip, it worked fine and was very usefull, but suddenly after certain actions I made, it worked no more!
(For example: After pressing the ‘Cancel’ button in a Dialog Box or an Input box.)
First I tought, it is in connection with “Cancel” button.
But the solution was in the code.
If I write:
***
start= InputBox(“Which is the start?”)
If start= “” Then End
***
This is wrong, because of this ‘End’, the above described “SheetChange event” works no more!
But with this:
***
start= InputBox(“Which is the start?”)
If start= “” Then goto endlabel
***
(The “endlabel:” is directly in the above line of the ‘End Sub’)
The solution in summarized:
Avoid the use of ‘End’ in your code, because it stops the execution of this ‘Application’ event.
(There is no explanation, it is only my experience.
Gábor
Dick addresses a few important issues of why one should forsake events in worksheet and workbook code modules. For more on the subject of raising and consuming events, see the draft
Monitoring events
http://www.tushar-mehta.com/excel/vba/vba-XL%20events.htm