Making VBA add in available to all workbooks

[rsudra]

I have writtem some VBA code that is attached to a sheet which reacts the a cell change:

Sub Worksheet_Change(ByVal target As Range)

However, I actually want to write an add in that will react to any cell change event regardless of the sheet or workbook (or excel file) worked on. Think of my add in as a logger that logs every cell change in excel (primarily in terms of value but other properties are fine too)

Any thoughts on how to achieve this?
Thanks,
Raj

[royUK]

A worksheet-change event has to be in the workbook that is changing. I'm not sure what or why you would want to log with an addin

[rsudra]

Although my initial plan is to just log each change to a file, eventually I would like to send the event to a server (hence I am looking for a way, be it VBA or some other excel API to be able to catch all events regardless of the workbook loaded such that I can convey changes to the files being edited to a remote location).

[mikerickson]

You can create an application event.
1)Insert a code module, use the properties window to change its name to appEvent, and put this code in it.

Public WithEvents allXL As Application

Private Sub allXL_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
   Rem your code
End Sub

2) Put this in a normal code module

Dim appEvents As New appEvent

Sub startUp()
Set appEvents.allXL = Application
End Sub

startUp needs to run to declare the application event, so this in ThisWorkbook

Private Sub Workbook_Open()
Call startUp
End Sub

The your code will run whenever a cell is changed in any open workbook.