In order to trap events of the worksheet that was dynamically generated, I define one class module and one code module like the following:
Class Module:
Public WithEvents xlapp as Application
Public Sub xlapp_SheetPivotTableUpdate(ByVal Wb As Workbook)
MsgBox " Testing Trap Events! "
End Sub
standard module:
Public xlApplication as new ClsAppEvents
Public Sub TrapApplicationEvents()
Set xlApplication.xlapp = Application
End Sub
I call TrapApplicationEvents from auto_open macro to trigger the worksheet's event automatically.
My problem is the pivottableupdate event can not be triggered automatically, I must run the TrapApplicationEvents macro manually every time to trigger the event.
Is there anybody know why?
Thanks