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

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

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

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

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.

--

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.

--