Trigger VBA on double click

[kgkev]

Couple of questions.

I want to apply an advanced filter based on the cell that is double clicked.


This works

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

I have a few questions

1. The Sheets "Data" is deleted and recreated as part of previous VBA routine, therefore the beforedoubleclick event is removed.
Can I make an beforedoubleclick event for the whole workbook?

2. After the double click, the cell that was clicked is still selected for edit. How can I cancel this selection?

3. I want to clear the filter by pressing ESC on the keyboard. Is it possible to trigger an event from an ESC key press?

 

[Fluff]

You can use a workbook event like

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
   If Sh.Name <> "Data" Then Exit Sub
   Cancel = True
   Global_Filter
End Sub

There is no trigger for Esc.
And whilst it is possible to use the Esc key to run a macro, you will loose the normal functionality of that key.
You would probably be better of just clicking "Clear" on the Data tab.

 

[Fluff]

Another option would be to doubleclick on a certain cell to clear the filter like

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
   If Sh.Name <> "Data" Then Exit Sub
   Cancel = True
   If Not Intersect(Target, Sh.Range("A1:F1")) Is Nothing Then
      Global_Filter
   ElseIf Target.Address(0, 0) = "J1" Then
      Sh.ShowAllData
   End If
End Sub