Excel Add-In to catch application events

[amdahlj]

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:

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.

 

[amdahlj]

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:


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.

This problem persists. Any ideas?

 

[xenou]

It's an unusual error message - actually, I've never seen it before. I would try a test in a new empty workbook - see if you can get just the plain code to trap an application level event working (then incorporate it into your existing project once you're satisfied that the custom class works as expected).