Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you get a parent collection object to listen to all if it's child objects?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you get a parent collection object to listen to all if it's child objects?
See: http://www.cpearson.com/excel/Events.aspx
Down towards the bottom of the page. Tim "Ronald R. Dodge, Jr." wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you get a parent collection object to listen to all if it's child objects?
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" wrote in message ... See: http://www.cpearson.com/excel/Events.aspx Down towards the bottom of the page. Tim "Ronald R. Dodge, Jr." wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you get a parent collection object to listen to all if it's child objects?
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." wrote in message ... 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" wrote in message ... See: http://www.cpearson.com/excel/Events.aspx Down towards the bottom of the page. Tim "Ronald R. Dodge, Jr." wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you get a parent collection object to listen to all if it's child objects?
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 "Tim Williams" wrote in message ... 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." wrote in message ... 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" wrote in message ... See: http://www.cpearson.com/excel/Events.aspx Down towards the bottom of the page. Tim "Ronald R. Dodge, Jr." wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you get a parent collection object to listen to all if it's child objects?
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 "Tim Williams" wrote in message ... 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." wrote in message ... 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" wrote in message ... See: http://www.cpearson.com/excel/Events.aspx Down towards the bottom of the page. Tim "Ronald R. Dodge, Jr." wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Parent/Child Macro | Excel Programming | |||
Parent/Child Field | Excel Programming | |||
parent/child ranges | Excel Programming | |||
Sorting Parent Child | Excel Worksheet Functions | |||
Nested Withs of Multiple Objects (Parent-Child) | Excel Programming |