Worksheet-specific event handling needed

[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 ?

 

[NickHK]

I'm not clear what you are trying to do here.
Also your nomenclature is a little confusing. e.g.:
Set shtClass.Sht = Application
Set appClass.App = Application

I tend to use property Let/Set/Get instead of Public variable so you can
trace the code into the class modules.

Not sure what you mean by:

' 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

If you want to raise the event then do the same as in the other routine:
RaiseEvent testEvent("Raised event from EventClass RaiseIt Method")

Explain more what you are trying to achieve.

NickHK

 

[syswizard]

I'm not clear what you are trying to do here.
Also your nomenclature is a little confusing. e.g.:
Set shtClass.Sht = Application
Set appClass.App = Application

I tend to use property Let/Set/Get instead of Public variable so you can
trace the code into the class modules.

Not sure what you mean by:



Explain more what you are trying to achieve.

NickHK

Thanks Nick....what I learned from 4 hours on this, is the following:
1) Application triggered event procedures within a Class cannot Raise
events in that same class. At least I could not get it to work.
2) Application-level event procedure names are different than
Worksheet event procedure names...and no debugger in the world will
help you discover this...the events just won't "fire". Example:
Worksheet_Activate vs. _Activate. The first is application level, the
second is worksheet level.
3) I was finally able to get a SHEET specific version of this event
code to work so that Activate would only be triggered for Sheet1, but
alas, without the ability to create a parameterized CLASS Initializer,
code must be copied if it is needed in more than one sheet. Otherwise,
you have to promote the event-firing to the Application level which
will hit "all sheets". BUT....If you do that, then you must then
"trap" the event procedure triggered (Worksheet_Activate for
instance)and only permit it to continue execution for the specific
sheet or sheets of interest. If you've got 100 sheets in the workbook,
it will check ALL OF THEM to determine which one has been Activated;
this is very inefficent.
Whew. I learned a lot about Excel VBA's class limitations and event
firing.

 

[Chip Pearson]

1) Application triggered event procedures within a Class cannot Raise
events in that same class.

Not true. For example, create a new standard module named Module1 and two
class modules, Class1 and Class2.

In Class1, use
''''''''''''''''''''''''''''''''''''''''''''''
Public WithEvents App As Application
Public Event OnNew(Wb As Workbook)

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
RaiseEvent OnNew(Wb)
End Sub
''''''''''''''''''''''''''''''''''''''''''''''

In Class2, use
''''''''''''''''''''''''''''''''''''''''''''''
Public WithEvents Class1EVT As Class1

Private Sub Class1EVT_OnNew(Wb As Workbook)
MsgBox "Class1EVT_OnNew event in Class2" & _
"triggered by Event OnNew in Class1"
End Sub

''''''''''''''''''''''''''''''''''''''''''''''

In Module1
''''''''''''''''''''''''''''''''''''''''''''''
Dim C1 As Class1
Dim C2 As Class2

Sub RunTest()
Set C1 = New Class1
Set C1.App = Application

Set C2 = New Class2
Set C2.Class1EVT = C1
Workbooks.Add
End Sub

''''''''''''''''''''''''''''''''''''''''''''''

Run the code RunTest. You'll get a message box from the OnNew event in
Class2. This event is defined in Class1 and raised by the Application event
App_NewWorkbook in Class1 using RaiseEvent. Thus, Class1 can sink
Application events and also create events of its own, even raising events in
the App's event procedure. No problems at all.

2) Application-level event procedure names are different than
Worksheet event procedure names...and no debugger in the world will
help you discover this...the events just won't "fire". Example:
Worksheet_Activate vs. _Activate. The first is application level, the
second is worksheet level.

and no debugger in the world will
help you discover this

Not True.

Learn to use and love the Object Browser (F2 in VBA). The Object Browser
will list all the events, their names, and their procedure declarations for
the Application Object or any other object that raises events. When working
with event, you will find your life MUCH easier if you use the event code
that VBA will generates for you. This ensures the proper event name and
declaration.

In an object module such as a Class module, click the left-hand dropdown at
the top of the code window, and choose the object whose events you are
interested. You must declare a variable of that object type in your code
using WithEvents. The name of the variable doesn't matter.

The right-hand dropdown will list all of the events for that object. Select
the event name in the right-hand dropdown, and VBA will insert the proper
declaration for that event. Don't change the code that VBA generates for
you.

Worksheet_Activate vs. _Activate. The first is application level, the
second is worksheet level.

"Worksheet_Activate" is NOT an application event, and the second is nothing
at all. "Worksheet_Activate" is an worksheet level event. The only object
that has an event name "*_Activate" is a worksheet. The application event
for a sheet activate is "App_SheetActivate" where "App" is the name of the
variable (of data type Application) that you declared using WithEvents.
E.g.,

Public WithEvents App As Application

The variable name "App" can be anything you want. Its name is part of the
event procedure name of the application event. Therefore, you could use

Public WithEvents MyExcelApp As Application

and your application events will have names like

MyExcelApp_SheetActivate

3) I was finally able to get a SHEET specific version of this event
code to work so that Activate would only be triggered for Sheet1, but
alas, without the ability to create a parameterized CLASS Initializer,
code must be copied if it is needed in more than one sheet.

Again, not True. The ThisWorkbook module has an event named
"Workbook_SheetActivate". This will fire when any sheet in the workbook is
activated. This event is passed an object that references the sheet that is
being activated. If you have the same code in each worksheet object module
for the worksheets, you can take it out of the Worksheet object module and
use "Workbook_SheetActivate". If the action of the code depends on what
worksheet is being activated, test the name of Sh object passed into
"Workbook_SheetActivate". This event handles all activation events for all
sheets in the workbook. If you are copying the same code into each
Worksheet's object module, you're doing things wrong. Use
the"Workbook_SheetActivate" event for all worksheets.

sheet or sheets of interest. If you've got 100 sheets in the workbook,
it will check ALL OF THEM to determine which one has been Activated;
this is very inefficent.

Yet again, not true. The workbook SheetActivate tells you exactly which
sheet is being activated. Use the workbook SheetActivate event if you have
the same of similar code for the activation of a sheet. If the code is
significanly different for differents, the use the Sheet's Activate event.

If you need events only for a few particular worksheets, you can create
WithEvents objects in the ThisWorkbook object module and you will receive
event trigger only for those worksheets. E.g, in ThisWorkbook, use

''''''''''''''''''''''''''''''''''''''''''''''
Public WithEvents SheetOne As Worksheet
Public WithEvents SheetTwo As Worksheet

Private Sub SheetOne_Activate()
MsgBox "Activate: " & SheetOne.Name
End Sub

Private Sub SheetTwo_Activate()
MsgBox "Activate: " & SheetTwo.Name
End Sub
''''''''''''''''''''''''''''''''''''''''''''''

You can initialize the SheetOne and SheetTwo objects either from code in a
regular code module, e.g,

Sub SetupSheets()
Set ThisWorkbook.SheetOne = Worksheets("Sheet1")
Set ThisWorkbook.SheetTwo = Worksheets("Sheet2")
End Sub

or in the ThisWorkbook's Open event:

Private Sub Workbook_Open()
Set Me.SheetOne = ThisWorkbook.Worksheets("Sheet1")
Set Me.SheetTwo = ThisWorkbook.Worksheets("Sheet2")
End Sub

Whew. I learned a lot about Excel VBA's class limitations and event
firing.

I think you have a very long way to go to understand classes and events.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[syswizard]

Thanks much Chip for the examples and clarification; no question, you
are right on my lacking the indepth experience you possess. However,
I've seen very little in the way of detailed documentation and sample
code regarding classes and events in the VBA books I've read.