Listen to double click

[chrisjones]

I have a made a custom class called "EventRange" which contains a range, when that range is doubleclicked I would like to be able to respond to that event. I have attached what I have done so far, however every time I double click on cell A1 nothing happens.


This is the test sub

Public Sub testEventsClass()
    Dim e As EventRange
    Set e = New EventRange
    
    Dim r As Range
    Set r = Range("A1")
    
    Set e.Range = r
End Sub

This is the EventRange Class

 
Private WithEvents mwksWorkSheet As Excel.Worksheet
Private r As Range
Public Property Set Range(ByVal value As Range)
    Set r = value
End Property
Public Property Get Range() As Range
    Set Range = r
End Property

Private Sub mwksWorkSheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Debug.Print "Range " & Target.Address & " has been double clicked"
End Sub

Thanks

Chris

[mikerickson]

The double click event is of the worksheet mwksWorkSheet, yet the code never sets that variable. Adding a line to the Property Set routine takes care of that.

The other problem is that e is dimensioned at the procedure level. When End Sub (of sub testEventsClass) exicutes, e passes out of scope, so there is no mwksWorkSheet to respond to events.

To make e persisistant, it has to be either a Public variable or Static.

mwkesWorkSheet is the whole sheet so, to make it respond only if the double clicked cell is in r, Application.Intersect should be used.

Public Sub testEventsClass()
    Static e As EventRange
    Set e = New EventRange
    
    Dim r As Range
    Set r = Range("A1")
    
    Set e.Range = r
End Sub

Private WithEvents mwksWorkSheet As Excel.Worksheet
Private r As Range

Public Property Set Range(ByVal value As Range)
    Set r = value
    Set mwksWorkSheet = value.Parent
End Property

Public Property Get Range() As Range
    Set Range = r
End Property

Private Sub mwksWorkSheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Application.Intersect(Target, r) Is Nothing Then
        MsgBox "Range " & Target.Address & " has been double clicked"
    End If
End Sub

[chrisjones]

Thanks mikerickson, that has sorted it. I see where I was going wrong however I am surprised that you can not add a listener directly to a range. Instead you seem to be adding a listner to the entire worksheet and just checking if the right cell has been clicked

Chris

[mikerickson]

Range objects don't have events, worksheet objects do.
If this isn't an exercise in creating custom Classes, I think you might be better off just using the standard DoubleClick event of either the worksheet or the ThisWorkbook.