How do you get a parent collection object to listen to all if it's child objects?

[Ronald R. Dodge, Jr.]

I am looking for a way of getting a parent collection object to be able to
hear all of it's child objects via events similar to how the Workbooks Event
of "SheetChange" work in listening to when there's a change to any of it's
sheets. How can I setup a such mechanism?

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Tim Williams]

See: http://www.cpearson.com/excel/Events.aspx

Down towards the bottom of the page.

Tim

 

[Ronald R. Dodge, Jr.]

I understand about creating events in general, but what I want to do is have
either the parent collection object or the parent of that collection object
to listen to all of the child objects of the parent collection object.

Example:

I know the Event itself goes in the Child Object (Source Class).

I know the raising of the event is in the Child Object (Source Class)

I also know the Source Object must be declared within the Sink Module.

What I don't want to have to do is create a different object variable for
each child object within the sink class module that is suppose to listen for
those events, but rather like to emulate how the "SheetChange" event is
handled.

What I'm getting at, how is it that the "ChangeSheet" within the sink class
module of "ThisWorkbook" module is able to listen to every single sheet
within the workbook rather than to just one sheet?

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Tim Williams]

Ronald,

You're right - I hadn't thought it through for the case of a collection or
array of objects.
Not as easy as I'd thought...

One way to do it (without class events as such, though below I'm using a
worksheet field just as an existing object able to raise an "internal"
event...) would be to pass a reference to the "container" object into each
member object as it's added to the collection or array. That way it can use
this reference to create a "callback" to it's "parent" class.

Eg:


' ---- in 'clsSheet' class module
Option Explicit
Private objOwner As Object
Public WithEvents ws As Excel.Worksheet

Private Sub ws_Change(ByVal Target As Range)
If Not objOwner Is Nothing Then
CallByName objOwner, "MemberChanged", 1, Target
End If
End Sub

Sub Init(sht As Excel.Worksheet, Optional owner As Object)
Set ws = sht
If Not owner Is Nothing Then Set objOwner = owner
End Sub
'----- end clsSheet



'------ In another class module (tested in `Sheet1`)
Option Explicit
Private osheets As Collection

Sub Setup()
Dim s As Worksheet, obs As clsSheet
Set osheets = New Collection
For Each s In ThisWorkbook.Worksheets
Set obs = New clsSheet
obs.Init s, Me
osheets.Add obs
Next s
End Sub

Sub MemberChanged(ByRef v As Object)
MsgBox v.Parent.Name & " changed sheet at: " _
& v.Address()
End Sub
'----- end code in other class module

Too much coupling between the member and container code.
Not great, but it will "work".

You might also Google for VBA+"control array" for ideas, depending on what
it is you're looking to set up.

Tim

 

[Ronald R. Dodge, Jr.]

Here's the only thing that I can think of.

Child Object must have a Parent Collection Object Property

At the time when the Child Object is created and added to the Parent
Collection Object, a reference to the Parent Collection Object is set in the
Parent Object Property of the Child.

Parent Collection Object must then have both the Event setup and a public
method that retrieves a pointer to the Child Object (via the ByVal method,
not the ByRef method), and then raise the Event within the Parent Collection
Object passing on the reference of the Child Object.

The Sink Module with the declared variable object using the WithEvents
keyword within the declaration statement for the Parent Collection Object is
then able to listen to the Event and know which child object triggered it

The child object may call on it's parent public method to raise the event
rather it be directly from within a property or a method of the child
object.

Example:

Public Property Let prp_rw_Parent(ByVal l_objWorkOrders As clsWorkOrders)
m_objWorkOrders = l_objWorkOrders
End Property
Public Property Get prp_rw_Parent() As clsWorkOrders
prp_rw_Parent = m_objWorkOrders
End Property


Public Property Let prp_rw_Status(ByVal l_lngStatus As g_WorkOrderStatus)
m_dteEndTime = Now()
'This is needed to raise the parent collection event so as production
times
' can be recorded against the work order. For JDE requires data to be
' recorded separately from the work order itself. Production times for
' each line is also needed to be taken into account.
m_objWorkOrders.RaiseOrderStatusUpdate Me
'After production times has been recorded, the status of the job may be
updated.
m_lngStatus = l_lngStatus
m_dteStartTime = m_dteEndTime
End Property.

The parent collection object would then raise the event with this child
object variable passed on.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Ronald R. Dodge, Jr.]

I have used a similar type coding for creating my own custom CauseValidation
property and Validate event within Access forms to similate VB6 version of
that property and event respectively.

Here's the code I have so far put in to address the issue, which has
actually opened up other possibilities and given me some other ideas as to
how to deal with some of the other issues that I have seen happen as well as
thought of happening:



Child Source Class Property to refer to Parent Collection Object

-----Start Of Code------

Public Property Set prp_rw_Parent(ByVal l_objWorkOrders As clsWorkOrders)
Set m_objWorkOrders = l_objWorkOrders
End Property
Public Property Get prp_rw_Parent() As clsWorkOrders
Set prp_rw_Parent = m_objWorkOrders
End Property

-----End Of Code------



Method to call on Parent Collection Object to raise the event both before
and after updating the child object. The before event so as the previous
step could be recorded into other places that needs to be recorded, and the
after update to allow for other objects get data about the current new step
of the order and what they may need to do with their portion about the order
(I.e. If it went from inactive to active, must make it active in their own
respective objects).

-----Start Of Code------

Public Function fncRecordStatusReason(ByVal l_lngStatus As
g_enmWorkOrderStatus, Optional ByVal l_strReasonCode As String = "") As Long
Dim l_lngActiveBeforeChange As Long, l_lngActiveAfterChange As Long
If m_dteStartTime > 0 Then
l_lngActiveBeforeChange = 1
Else
l_lngActiveBeforeChange = 0
End If
m_dteEndTime = Now()
If (m_lngStatus = l_lngStatus And m_strReasonCode = l_strReasonCode And
m_dteStartTime > 0) Or m_dteStartTime > m_dteEndTime Or _
VBA.Format(m_dteStartTime, "dd/mm/yyyy hh:mm:ss") =
VBA.Format(m_dteEndTime, "dd/mm/yyyy hh:mm:ss") Then
Else
m_objWorkOrders.pcdWorkOrderStatusBeforeUpdate Me
pcdRecordProductionTime
End If
m_lngStatus = l_lngStatus
m_strReasonCode = l_strReasonCode
Select Case l_lngStatus
Case g_enmWorkOrderStatus.lngWorkOrderRunEnum,
g_enmWorkOrderStatus.lngWorkOrderRunIdleEnum,
g_enmWorkOrderStatus.lngWorkOrderRunProblemEnum, _
g_enmWorkOrderStatus.lngWorkOrderSetupEnum,
g_enmWorkOrderStatus.lngWorkOrderSetupIdleEnum,
g_enmWorkOrderStatus.lngWorkOrderSetupProblemEnum
If l_strReasonCode = "P" Or l_strReasonCode = "T" Then
m_dteStartTime = 0
l_lngActiveAfterChange = 0
Else
m_dteStartTime = m_dteEndTime
l_lngActiveAfterChange = 1
End If
Case Else
m_dteStartTime = 0
l_lngActiveAfterChange = 0
End Select
m_objWorkOrders.pcdWorkOrderStatusAfterUpdate Me,
l_lngActiveAfterChange - l_lngActiveBeforeChange
fncRecordStatusReason = l_lngActiveAfterChange - l_lngActiveBeforeChange
End Function


-----End Of Code------



Parent Collection Object Event Codes

-----Start Of Code------

Public Event evtWorkOrderStatusBeforeUpdate(ByVal l_objWorkOrder As
clsWorkOrder)
Public Event evtWorkOrderStatusAfterUpdate(ByVal l_objWorkOrder As
clsWorkOrder, ByVal l_lngOrderActiveStatusChangeValue As Long)


-----End Of Code------



The raising of the events withing the Parent Collection Object

-----Start Of Code------

Public Sub pcdWorkOrderStatusAfterUpdate(ByVal l_objWorkOrder As
clsWorkOrder, ByVal l_lngOrderActiveStatusChangeValue As Long)
RaiseEvent evtWorkOrderStatusAfterUpdate(l_objWorkOrder,
l_lngOrderActiveStatusChangeValue)
End Sub
Public Sub pcdWorkOrderStatusBeforeUpdate(ByVal l_objWorkOrder As
clsWorkOrder)
RaiseEvent evtWorkOrderStatusBeforeUpdate(l_objWorkOrder)
End Sub


-----End Of Code------



The Sink Class Module of the Declaration of the Source Class object

-----Start Of Code------

Dim WithEvents m_WorkOrders As clsWorkOrders


-----End Of Code------



The signature line of the Sink Class Module receiving the before update
code.

-----Start Of Code------

Private Sub m_WorkOrders_evtWorkOrderStatusBeforeUpdate(ByVal l_objWorkOrder
As clsWorkOrder)


-----End Of Code------


--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000