Creating An Application Event Handler

[HLong]

I am not very familiar with Excel Addins jet. I am trying to create an addin
with an event handler that will fire up on Application.SheetChange. I create
a class object and declared a variable withevents, then added the event
handler. Also, created a module object with two routines to instantiate and
terminate the class object. Here is where I got lost, if I instantiate the
class thru a routine on ThisWorkbook object, everything works fine. However,
if I create an addin thisWorkbook will never be active and the class object
can't be instantiated. So, my problem is: How do I instantiate this class so
the application events are fired when any workbook is opened. I need to fire
these events at all time. I don't know if this is the correct approach. All
suggestions will be greatly appreciated.

 

[Dave Peterson]

Your addin doesn't have to be the activeworkbook for things to work.

Were you using the Workbook_open event or something else?

 

[HLong]

Thanks Dave,
At this point I haven't created the addin yet. I have completed the workbook
and tested the event handlers. I have never used an addin. I don't know how
I could make the adding to instatiate the class thru the module when excel is
started. I know that once I create the addin from the workbook and check it

 

[Dave Peterson]

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_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox Sh.Name & vbLf & Target.Address
End Sub

Is a small example (all under ThisWorkbook).

Save it as an addin and it won't be visible to the user.

Put it in your XLStart and it'll load each time excel starts.

Or use Tools|addins to point to it so that it loads each time xl starts.