Creating An Application Event Handler
 

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.

Creating An Application Event Handler
 

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

Were you using the Workbook_open event or something else?

HLong wrote:

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

Creating An Application Event Handler
 

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
on the addin manager, it would be loaded everytime excel is started. Do you
know how to do this dave? Could you give me a hand?

"Dave Peterson" wrote:

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

Were you using the Workbook_open event or something else?

Creating An Application Event Handler
 

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.



HLong wrote:

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
on the addin manager, it would be loaded everytime excel is started. Do you
know how to do this dave? Could you give me a hand?

"Dave Peterson" wrote:

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

Were you using the Workbook_open event or something else?


--

Dave Peterson