Create Custom Events using Withevents in VBA

WithEvents specifies that one or more declared member variables refer to an instance of a'
class that can raise events. WithEvents connects the event system to the  variable and
lets you utilize the events of the object.

In this example; we have raised cellSelect event. On selection of value in "A1"; we can
get cell name, cell address, colorindex and cell content in column B,C,D,E.

Steps:
 1.Declare a variable with  WithEvents and classname in Sheet where we need to raise
events.

 2.Write attributes and methods in a class, There are three attributes, for selecting
range, cell name, &set color and one method for setting color in selected range.

3.Declare 3 variables for three attributes for selected range, color and name.

4.Declare an event cellSelect.

5.RaiseEvent inside set property for selected Range.

6.Write a method named methodColor for assigning colorindex to a selected cell.

              Dim rngVar As Range
              Dim intColor As Integer
              Dim strName As String

              Public Event cellSelect(cell As Range)

   Public Property Set selectedRng(objVar As Range)
            Set rngVar = objVar
            RaiseEvent cellSelect(rngVar)
   End Property

   Public Property Get selectedRng() As Range
          Set selectedRng = rngVar
   End Property

  Public Property Let Name(objName As String)

         strName = objName
  End Property

  Public Property Get Name() As String
         Name = strName
  End Property

  Public Property Let Color(objColor As Integer)
        intColor = objColor

  End Property
  Public Property Get Color() As Integer
         Color = intColor
  End Property

  Sub methodColor()

        selectedRng.Interior.ColorIndex = Color

  End Sub

7.Now write an event procedure rng_cellSelect

8.Now assign  selected Range under Private Sub
  Worksheet_SelectionChange(ByVal Target As Range)




Private WithEvents rng As clsWithEvents
Dim i As Integer
Private Sub rng_cellSelect(cell As Range)
    rng.Color = 24
    If (rng.Color < 1) And (rng.Color > 56) Then
            MsgBox "Error! please enter a color index between 1 and 56"
    End If
        rng.Name = "First Cell"
        rng.methodColor
        rng.selectedRng.Select
        i = rng.Color
        Selection.Offset(0, 1).Value = "Name: " & rng.Name
        Selection.Offset(0, 2).Value = "Address:  " & Selection.Address
        Selection.Offset(0, 3).Value = "Interior color Index:  " & i
        Selection.Offset(0, 4).Value = "Cell Content  " & Selection.Value
       
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Set rng = New clsWithEvents
       
            If (Target.Address = Range("A1").Address) Then
                Set rng.selectedRng = Target
           
           
            End If
End Sub
Download File