Control double click event for row header

Question

So I'm pretty familiar with referencing worksheet ranges for worksheet events such as double click. In this case though, I'm looking to reference when the row header gets double clicked instead of a cell. It would still be specific to a worksheet but I've been unsuccessful thus far.

I have multiple ranges that do different events on double clicks so I use code similar to the example below:

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

    Set rWatchRange = Range("A5:A1000")

    'I somehow need it to recognize if the row header is 
    'double clicked between row 5 and 1000 to fire off the second sub

    Set sWatchRange = Range("5:1000")


    If Not Application.Intersect(Target, rWatchRange) Is Nothing Then
        Run "aFormattingSub"
    End If

    If Not Application.Intersect(Target, sWatchRange) Is Nothing Then
        Run "aSubToInsertNewLineAndGroupWithRowAbove"
    End If      
End Sub

I'm not sure if there is a worksheet reference, application reference or a setting in Excel that I'm aware of that can do this.

When you try to double click a column header the Worksheet_SelectionChange event is fired with a full column selected. — Tin Bum

KekuSemau KekuSemau · Accepted Answer · 2014-09-05T08:52:11

The DoubleClick Event does not fire when the headers are doubleclicked. I don't think there is any trivial way around this - you have to live with the events as they are provided.

I think there are still enough room to implement more functionality.
To give you some more ideas, you could do different things on a double click or right click with ctrl held down.

An example that reacts to the right click with ctrl held down, and only when entire rows are selected:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If (GetKeyState(KeyCodeConstants.vbKeyControl) And &H8000) And _
        Selection.Address = Selection.EntireRow.Address Then

        Cancel = True
        ' ... code
    End If
End Sub

(the And &H8000 is necessary to react only to currently present and ignore previous keypresses)

Import the API function in a module:

Public Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer

Control double click event for row header

1 Answers