Custom events

[tiredofit]

This is in Sheet1:

Private WithEvents rng As clsRange

Private Sub rng_CellSelect(cell As Range)

rng.Color = 4

If rng.Color < 1 Or rng.Color > 56 Then
MsgBox "Error! Enter a value for ColorIndex between 1 and 56"
Exit Sub
End If

rng.Name = "FirstCell"

rng.methodColor

Dim i As Integer
i = rng.Color

rng.selectedRange.Select
Selection.Offset(0, 1).Value = "Cell Name: " & rng.Name
Selection.Offset(0, 2).Value = "Cell Address: " & Selection.Address
Selection.Offset(0, 3).Value = "Cell Interior ColorIndex: " & i
Selection.Offset(0, 4).Value = "Cell Content: " & Selection.Value

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("A1").Address Then Call Module1.gr(Target)

On Error GoTo ErrorHandler  'Enable error-handling routine for any run-time error

Set rng = New clsRange

If Target.Address = Range("A1").Address Then
Set rng.selectedRange = Target
Else
Exit Sub
End If

ErrorHandler:
  Application.EnableEvents = True  'EnableEvents is changed back to True on any error

End Sub

This is in ClsRange:

Private varRng As Range
Private intColor As Integer
Private strName As String

Public Event CellSelect(cell As Range)

Public Property Set selectedRange(objRng As Range)

Set varRng = objRng

RaiseEvent CellSelect(varRng)
   
End Property

Public Property Get selectedRange() As Range

Set selectedRange = varRng

End Property

Property Let Name(nm As String)

strName = nm

End Property

Property Get Name() As String

Name = strName

End Property

Property Let Color(clr As Integer)

intColor = clr

End Property

Property Get Color() As Integer

Color = intColor

End Property

Sub methodColor()

selectedRange.Interior.ColorIndex = Color

End Sub

When the contents of cell A1 is changed, the event kicks off.

What I don't understand is why go through all this when you could easily just put the code in Sheet1, for example:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("A1").Address Then

    ' do something

End If

End Sub

and not need RaiseEvent, Event and WithEvents?

Thanks

 

[RoryA]

Your class can work with any range at all. If you put the code in a worksheet, it's specific to that sheet. It's all about encapsulation. I suspect that this is also more of an example than something that is necessarily that practical.

 

[tiredofit]

Your class can work with any range at all. If you put the code in a worksheet, it's specific to that sheet. It's all about encapsulation. I suspect that this is also more of an example than something that is necessarily that practical.

"Your class can work with any range at all. If you put the code in a worksheet, it's specific to that sheet. It's all about encapsulation. "

A bit like application level events, when you want all workbooks to trigger printing?

 

[RoryA]

Vaguely. It allows you to effectively monitor ranges without adding code to the target workbook.

 

[tiredofit]

Vaguely. It allows you to effectively monitor ranges without adding code to the target workbook.

Thanks