Workbook properties code in the xla addin
I'm not a master of event programming but a couple of considerations:
(1) I'm not sure if you'd be better off declaring 'as new'. Unless I
missed something I'd declare it:
Public xlApplication As ClsAppEvent
i.e., without the NEW in it and then create the object in the code.
As far as I know it's not the best practice to declare as New as
otherwise you've got no way of testing if the variable has been
instantiated although for this particular purpose the NEW could be
good enough.
(2) I think that your problem could be in firing this code of yours:
Public Sub TrapApplicationEvents()
Set xlApplication.xlapp = Application
End Sub
i.e., this code must be run before all the event trapping can happen -
if this code hasn't run you don't have your xlApplication variable
created yet and because of that it obviously can't trigger any events.
So, I think that you don't run the code in the beginning.
To test it - just run the code separately - in VBE locate this code
and run it (F5) - it would create the xlApplication variable and from
then on it should trap the events BUT bear in mind that this variable
will cease to exist if you edit the code or close the file so what you
need to do is to make sure that this code runs before you want to
start trapping the events - so, perhaps you could put it in the
Workbook_Open event of the xla file something like this:
Private Sub Workbook_Open()
Set xlApplication.xlapp = Application
End Sub
This would be then in the ThisWorkbook module of the xla file - this
way whenever the xla opens it creates your variable for event trapping
and it should be fine now.
|