PDA

View Full Version : Listen to double click



chrisjones
08-02-2010, 08:28 AM
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
08-02-2010, 09:32 PM
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
08-03-2010, 12:46 AM
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
08-03-2010, 06:09 AM
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.