Excel Add-In to catch application events

Jul 15, 2011
Reaction score
I'm trying to write a sub which will run while an add-in is open and the user opens a workbook. The best source I found for doing this gives the following instructions:
http://www.cpearson.com/excel/Events.aspx said:
The second approach to creating Application Events is to use a dedicated class module. Insert a class module into your project and name the class module CExcelEvents. In that class module, enter the following code:

Private WithEvents XLApp As Application

Private Sub Class_Initialize()
Set XLApp = Application
End Sub
Then, change the left side dropdown at the top of the code pane to XLApp and choose an event from the right side dropdown. VBA will automatically insert the proper procedure shell for that event. As before, do not change the code generated by VBA.

You can then define your application event procedures in the class module. For example,

Private Sub XLApp_NewWorkbook(ByVal Wb As Workbook)
MsgBox "NewWorkbook" & Wb.Name
End Sub

The problem is that when I select XLApp from the dropdown, I get the error message "A Module is not of a valid type". This occurs whether or not I use the class module method or the ThisWorkbook method for handling application events but does not happen if I use the code in a regular excel file rather than an Add-in. Copying the code which is supposed to result from that action does not result in working code (events are not caught).

I'd really like to incorporate this functionality into an existing add-in I have developed. Any ideas on how to get it working?

Thank you in advance for any help.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question