Linking a range in a class to a cell, with change notification
Hi,
I would like to do a thing, but I do not know if it is possible: I have a class, where I defined an object: a range of a single cell. I would like to know if there is a way to raise a "change" event whenever the referred cell value changes. I know that this can be done using a sheet module and a Worksheet_Change event, but I need to have tat event directly in classes, since I cannot use sheets modules. If this cannot be done do you have any idea about how I can detect a cell change from a class? Bye & tks Luca |
Linking a range in a class to a cell, with change notification
It kind of sounds like you may want to use an application event (narrowed only
to look at a certain workbook/worksheet/range)??? You can read a lot more about application events at Chip Pearson's site: http://www.cpearson.com/excel/AppEvent.htm === Could you use "Workbook_SheetChange" under the ThisWorkbook (narrowed to look at that certain worksheet/range)??? Luca wrote: Hi, I would like to do a thing, but I do not know if it is possible: I have a class, where I defined an object: a range of a single cell. I would like to know if there is a way to raise a "change" event whenever the referred cell value changes. I know that this can be done using a sheet module and a Worksheet_Change event, but I need to have tat event directly in classes, since I cannot use sheets modules. If this cannot be done do you have any idea about how I can detect a cell change from a class? Bye & tks Luca -- Dave Peterson |
Linking a range in a class to a cell, with change notification
I'm a little confused. A single cell as I understand it is a range. Why do
you need a class for this ? "Luca" wrote: Hi, I would like to do a thing, but I do not know if it is possible: I have a class, where I defined an object: a range of a single cell. I would like to know if there is a way to raise a "change" event whenever the referred cell value changes. I know that this can be done using a sheet module and a Worksheet_Change event, but I need to have tat event directly in classes, since I cannot use sheets modules. If this cannot be done do you have any idea about how I can detect a cell change from a class? Bye & tks Luca |
Linking a range in a class to a cell, with change notification
On Tue, 8 Mar 2005 19:13:03 -0800, gocush
/delete wrote: I'm a little confused. A single cell as I understand it is a range. Why do you need a class for this ? hi, actually the class must perform some actions on other objects in response to a change of that cell value. that's why I am using a class, moreover it should be possible to "link" different cells to different actions to be triggered, that's why a class is a good solution. So the problem is let the class receive an event whenever a specific cell value changes, I wondered to use a range and to link it in some way to the cell value, but I cannot figure out how to raise a change event to trigger the needed actions. If you want to see it in another way: emulate the worksheet_change event WITHOUT using modules, just classes. Bye & tks Luca |
Linking a range in a class to a cell, with change notification
Luca wrote: I cannot figure out how to raise a change event to trigger the needed actions. Here's a suggestion: ' ---<Code in class module named Class1--- Option Explicit Private WithEvents m_Worksheet As Excel.Worksheet Private m_Range As Excel.Range Public Property Set Range(ByVal RHS As Excel.Range) ' validation e.g. If RHS Is Nothing Then Err.Raise _ vbObjectError + 1, TypeName(Me), _ "Invalid Range object." End If If RHS.Rows.Count 1 Or RHS.Columns.Count 1 Then Err.Raise _ vbObjectError + 2, TypeName(Me), _ "Must be single cell Range object." End If Set m_Range = RHS Set m_Worksheet = m_Range.Worksheet End Property Public Property Get Range() As Excel.Range Set Range = m_Range End Property Private Sub m_Worksheet_Change( _ ByVal Target As Range _ ) If m_Range Is Nothing Then Exit Sub End If Dim oIntersection As Excel.Range Set oIntersection = _ Excel.Application.Intersect(Target, m_Range) If oIntersection Is Nothing Then Exit Sub End If MsgBox "TODO: take action here" End Sub ' ---<Code in class module named Class1--- ' ---<Code in ThisWorkbookcode module--- Option Explicit Private m_Class1 As Class1 Private Sub Workbook_Open() Set m_Class1 = New Class1 Set m_Class1.Range = Sheet1.Range("B2") ' Trigger event in class Sheet1.Range("B2").Value = 0 End Sub ' ---</Code in ThisWorkbookcode module--- Jamie. -- |
Linking a range in a class to a cell, with change notification
Of course, you may want to take action when validation fails <g, so
more like this: ' validation e.g. If RHS Is Nothing Then Err.Raise _ vbObjectError + 1, TypeName(Me), _ "Invalid Range object." Exit Property End If Jamie. -- |
All times are GMT +1. The time now is 08:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com