I have been trying to devolve most of my VBA code from a spreadsheet
to a DLL, and have had some success.

However, I am currently stumped. I am trying without success to put my
code for Application events into the DLL, but I can't get the events
to fire up as I expected. Can anyone suggest what I might be doing
wrong?

The following simple code fragment has been put into an ActiveX DLL
created in VB6. I have named the project EventLib. I have added the
Excel 11.0 and Office 12.0 object libraries to my project references.
Currently, the project has one class module, which I have named
clsXLEvents, following Chip Pearson's advice.


Option Explicit

Private WithEvents Appl As Application

Private Sub Class_Initialize()
MsgBox "Creating Instance", vbOKOnly, "clsXLEvents"

Set Appl = Application
End Sub

Private Sub Appl_NewWorkbook(ByVal Wb As Excel.Workbook)
MsgBox "NewWorkbook: " & Wb.Name, vbOKOnly, "DLL clsXLEvents"
End Sub

Private Sub Appl_WorkbookBeforeClose(ByVal Wb As Excel.Workbook,
Cancel As Boolean)
MsgBox "WorkbookBeforeClose: " & Wb.Name, vbOKOnly, "DLL
clsXLEvents"
End Sub

Private Sub Appl_WorkbookBeforeSave(ByVal Wb As Excel.Workbook, ByVal
SaveAsUI As Boolean, Cancel As Boolean)
MsgBox "WorkbookBeforeSave: " & Wb.Name, vbOKOnly, "DLL
clsXLEvents"
End Sub

Private Sub Appl_WorkbookOpen(ByVal Wb As Excel.Workbook)
MsgBox "WorkbookOpen: " & Wb.Name, vbOKOnly, "DLL clsXLEvents"
End Sub

<<

I have created a simple Excel workbook, called Events.xls, and added
the EventLib.dll to my VBA references in the Tools menu.

I have placed the following code in ThisWorkbook


Option Explicit

Private XLAppl As EventLib.clsXLEvents

Private Sub Workbook_Open()
Stop
Set XLAppl = New EventLib.clsXLEvents
End Sub

<<

I was hoping to see output from the MsgBox'es when I opened an new
workbook, saved a workbook, etc.

However, no such events were trapped.

Clearly, I am missing something, but I don't know what!

Has anyone tried anything similar to this, and had any success?

Thank you in anticipation.