Would Like to Handle BeforeDoubleClick Event Or SelectionChange Event. But Not Both.

[CurtisD]

The user of a worksheet wants a certain functionality. If the user clicks a cell the font for the cell should be changed to bold. But if the user double clicks a cell he/she wants the fill color to change to red and does not want to change the font. I don't see how this can be done. Because when the user double clicks a cell the SelectionChange event sub executes first. And if it can't detect that the event was a double click it won't know to simply exit the SelectionChange event sub and allow the BeforeDoubleClick event sub to begin.

But perhaps there is a way to detect that a double click event occurred when the SelectionChange event sub begins. Does anyone know? Any suggestions on solving this would be appreciated.

 

[GWteB]

perhaps there is a way to detect that a double click event occurred when the SelectionChange event sub begins.

Not that I know of. Probably it would be possible with some WinAPI trickery, but I wouldn't take that route.
Note that relying on events can be tricky, because it's not always clear in which order Excel actually fires events when events occur at the same time.

Regarding your font and color issue, if you explicitly specify the desired font in the BeforeDoubleClick event handler, like Target.Font.Bold = False then you undo a previous, unwanted change.

Finally, I would like to note that a double click puts the cell in question in edit mode. To exit this mode, pressing the ESC key or selecting another cell is needed. Users could find this very annoying in the long run.

 

[mumps]

Mis-posted

 

[Jaafar Tribak]

See if this works for you for distinguishing between Cell click and double-click

In the Worksheet Module:

Option Explicit

Private Enum MouseAction
    Click
    DoubleClick
End Enum

#If VBA7 Then
    #If Win64 Then
        Private Declare PtrSafe Function GetTickCount Lib "kernel32" Alias "GetTickCount64" () As LongLong
    #Else
        Private Declare PtrSafe Function GetTickCount Lib "kernel32" () As Long
    #End If
    Private Declare PtrSafe Function GetDoubleClickTime Lib "user32" () As Long
#Else
    Private Declare Function GetTickCount Lib "kernel32" () As Long
    Private Declare Function GetDoubleClickTime Lib "user32" () As Long
#End If

Private bDblClicked As Boolean
Private oPrev As Range


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    #If Win64 Then
        Dim t As LongLong
    #Else
        Dim t As Long
    #End If
   
    Const FRACTION = 4 '<== change this const value to suit. (Experiment with 1 to 4)
   
    t = GetTickCount
    Do: DoEvents
    Loop While GetTickCount - t <= GetDoubleClickTime / FRACTION
   
    If bDblClicked = False Then
        On Error Resume Next
        If Target.Count = 1 Then
            If Err.Number = 0 Then
                On Error GoTo 0
                Call MyMacro(Target, Click)
            End If
        End If
    Else
        bDblClicked = False
    End If
   
    Set oPrev = Target

End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Cancel = True
    bDblClicked = True
   
    If Not oPrev Is Nothing Then
        If oPrev.Address = Target.Address Then
            bDblClicked = False
        End If
    End If
   
    Call MyMacro(Target, DoubleClick)

End Sub

Private Sub MyMacro(ByVal Target As Range, ByVal Action As MouseAction)
    If Action = Click Then
        Target = "Clicked"
    Else
        Target = "Dbl-Clicked"
    End If
End Sub

 

[CurtisD]

See if this works for you for distinguishing between Cell click and double-click

In the Worksheet Module:

Option Explicit

Private Enum MouseAction
    Click
    DoubleClick
End Enum

#If VBA7 Then
    #If Win64 Then
        Private Declare PtrSafe Function GetTickCount Lib "kernel32" Alias "GetTickCount64" () As LongLong
    #Else
        Private Declare PtrSafe Function GetTickCount Lib "kernel32" () As Long
    #End If
    Private Declare PtrSafe Function GetDoubleClickTime Lib "user32" () As Long
#Else
    Private Declare Function GetTickCount Lib "kernel32" () As Long
    Private Declare Function GetDoubleClickTime Lib "user32" () As Long
#End If

Private bDblClicked As Boolean
Private oPrev As Range


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    #If Win64 Then
        Dim t As LongLong
    #Else
        Dim t As Long
    #End If
  
    Const FRACTION = 4 '<== change this const value to suit. (Experiment with 1 to 4)
  
    t = GetTickCount
    Do: DoEvents
    Loop While GetTickCount - t <= GetDoubleClickTime / FRACTION
  
    If bDblClicked = False Then
        On Error Resume Next
        If Target.Count = 1 Then
            If Err.Number = 0 Then
                On Error GoTo 0
                Call MyMacro(Target, Click)
            End If
        End If
    Else
        bDblClicked = False
    End If
  
    Set oPrev = Target

End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Cancel = True
    bDblClicked = True
  
    If Not oPrev Is Nothing Then
        If oPrev.Address = Target.Address Then
            bDblClicked = False
        End If
    End If
  
    Call MyMacro(Target, DoubleClick)

End Sub

Private Sub MyMacro(ByVal Target As Range, ByVal Action As MouseAction)
    If Action = Click Then
        Target = "Clicked"
    Else
        Target = "Dbl-Clicked"
    End If
End Sub

Thank you. This may be the solution I need.

 

[mikerickson]

Another approach would be for the BeforeDoubleClick event to first un-do what the SelectionChange event does and then the Double Click effect.

 

[Jaafar Tribak]

Thank you. This may be the solution I need.

Glad the code worked for you. ?

Out of interest, did you have to adjust the value of the FRACTION constant or did you leave it as is (ie:= 4) ?

 

[CurtisD]

Glad the code worked for you. ?

Out of interest, did you have to adjust the value of the FRACTION constant or did you leave it as is (ie:= 4) ?

I was planning to try your code right after I posted my previous reply, but got bogged down with other things.
I'll test it shortly and will let you know if it truly solves my problem. Thanks.

 

[CurtisD]

I was planning to try your code right after I posted my previous reply, but got bogged down with other things.
I'll test it shortly and will let you know if it truly solves my problem. Thanks.

I've tested the code and it's excellent. I will probably set FRACTION to 1 or 2. Some users of the workbook are slower at double clicking than others,
so we need to wait longer to detect if the event is a double click. Thanks again!

 

[Jaafar Tribak]

I've tested the code and it's excellent. I will probably set FRACTION to 1 or 2. Some users of the workbook are slower at double clicking than others,
so we need to wait longer to detect if the event is a double click. Thanks again!

Thanks for the feedback.

There is however one serious issue with the code which I didn't think about: the Click code also fires when selecting cells with keyboard navigation keys (arrow, Home, pageUpDown keys etc).

This Shouldn't happen as the Click code should fire only when carrying out a mouse click on the cell.

The Worksheet_SelectionChange event doesn't distiguish between a mouse click and a selection with keyboard keys. So I have taken a slightly different approach which I will be posting in the next post.