S
syswizard
In the Sheet1 code, I have the following:
--------------------------------------------------------
Option Explicit
Dim shtClass As New EventClass
Dim appClass As New EventClass
Private WithEvents evtTest As EventClass
Private Sub Worksheet_Activate()
' The below statements when activated do nothing - no events are
triggered in EventClass
' Dim shtClass As New EventClass
' Dim appClass As New EventClass
' this permits an event raised in the Class to be trapped here
Set evtTest = New EventClass
' the below does nothing
Set shtClass.Ws = Application.ActiveSheet
' Two separate event triggers can be established
Set shtClass.Sht = Application
Set appClass.App = Application
Call evtTest.RaiseIt ' this is executed first, before the
Application events
End Sub
Private Sub evtTest_testEvent(strMsg As String)
MsgBox ("testEvent proc triggered with message:" & vbCrLf & strMsg)
End Sub
-----------------------------------------------------------------------------------------------------
In a class module called EventClass is this:
Option Explicit
Public WithEvents Ws As Worksheet
Public WithEvents App As Application
Public WithEvents Sht As Application
Public Event testEvent(strMsg As String)
Private Sub App_NewWorkbook(ByVal Wb As Excel.Workbook)
MsgBox "Application Event: New Workbook: " & Wb.Name
End Sub
Private Sub App_SheetActivate(ByVal Sh As Object)
MsgBox "Application Event: SheetActivate: " & Sh.Name
End Sub
Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook)
MsgBox "Application Event: WorkbookOpen: " & Wb.Name
End Sub
Private Sub Sht_SheetActivate(ByVal Sh As Object)
MsgBox "Sheet Event: SheetActivate: " & Sh.Name
' RaiseIt THIS CALL DOES NOT WORK - MUST BE CALLED FROM OUTSIDE OF
CLASS EXPLICTLY
' The below call does not work either
' Dim evt As EventClass
' Set evt = New EventClass
' evt.RaiseIt
End Sub
Public Sub RaiseIt()
RaiseEvent testEvent("Raised event from EventClass RaiseIt Method")
End Sub
Private Sub Ws_SheetActivate(ByVal Sh As Object)
MsgBox ("WS-Sheet Activation occurred")
End Sub
---------------------------------------------------------------------------------------------------------------------------------
There are two problems. First, I discovered I cannot raise a custom
event from within a class module....it just doesn't fire. Works fine
when called from another module or sheet or whatever.
Secondly, the above code fires the class methods
xxx_SheetActivate(....) for ALL WORKSHEETS, despite the fact the code
is only in Sheet1.
How can this be modified to ONLY fire the class methods for Sheet1
without having to hard-code a If-then-else condition ? In other words,
how can it be done by redefining the class properly to only cause
events to occur at Sheet1 ?
--------------------------------------------------------
Option Explicit
Dim shtClass As New EventClass
Dim appClass As New EventClass
Private WithEvents evtTest As EventClass
Private Sub Worksheet_Activate()
' The below statements when activated do nothing - no events are
triggered in EventClass
' Dim shtClass As New EventClass
' Dim appClass As New EventClass
' this permits an event raised in the Class to be trapped here
Set evtTest = New EventClass
' the below does nothing
Set shtClass.Ws = Application.ActiveSheet
' Two separate event triggers can be established
Set shtClass.Sht = Application
Set appClass.App = Application
Call evtTest.RaiseIt ' this is executed first, before the
Application events
End Sub
Private Sub evtTest_testEvent(strMsg As String)
MsgBox ("testEvent proc triggered with message:" & vbCrLf & strMsg)
End Sub
-----------------------------------------------------------------------------------------------------
In a class module called EventClass is this:
Option Explicit
Public WithEvents Ws As Worksheet
Public WithEvents App As Application
Public WithEvents Sht As Application
Public Event testEvent(strMsg As String)
Private Sub App_NewWorkbook(ByVal Wb As Excel.Workbook)
MsgBox "Application Event: New Workbook: " & Wb.Name
End Sub
Private Sub App_SheetActivate(ByVal Sh As Object)
MsgBox "Application Event: SheetActivate: " & Sh.Name
End Sub
Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook)
MsgBox "Application Event: WorkbookOpen: " & Wb.Name
End Sub
Private Sub Sht_SheetActivate(ByVal Sh As Object)
MsgBox "Sheet Event: SheetActivate: " & Sh.Name
' RaiseIt THIS CALL DOES NOT WORK - MUST BE CALLED FROM OUTSIDE OF
CLASS EXPLICTLY
' The below call does not work either
' Dim evt As EventClass
' Set evt = New EventClass
' evt.RaiseIt
End Sub
Public Sub RaiseIt()
RaiseEvent testEvent("Raised event from EventClass RaiseIt Method")
End Sub
Private Sub Ws_SheetActivate(ByVal Sh As Object)
MsgBox ("WS-Sheet Activation occurred")
End Sub
---------------------------------------------------------------------------------------------------------------------------------
There are two problems. First, I discovered I cannot raise a custom
event from within a class module....it just doesn't fire. Works fine
when called from another module or sheet or whatever.
Secondly, the above code fires the class methods
xxx_SheetActivate(....) for ALL WORKSHEETS, despite the fact the code
is only in Sheet1.
How can this be modified to ONLY fire the class methods for Sheet1
without having to hard-code a If-then-else condition ? In other words,
how can it be done by redefining the class properly to only cause
events to occur at Sheet1 ?