Linking a range in a class to a cell, with change notification

[Luca]

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]

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

[gocush]

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
>

[Luca]

On Tue, 8 Mar 2005 19:13:03 -0800, gocush
<gocushNOT_THIS@comcast.net/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

[Jamie Collins]

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.

--

[Jamie Collins]

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.

--