Multiple Double Click Events on Different Ranges in 1 Worksheet-CodePudding

I'm trying to simulate an interactive "dashboard" in Excel where double clicking specific ranges will Autofilter different parameters on another sheet.

I'll have a lot of them, probably >10. Here's generally what I have so far:

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

Application.EnableEvents = False
On Error GoTo ErrorRoutine

Dim Goal As String
Goal = "*" & Cells(Target.Row, 7) & "*"

If Not Intersect(Target, Range("L7:L166")) Is Nothing Then

    On Error Resume Next
    Sheets("Details").ShowAllData
    
    Sheets("Details").UsedRange.AutoFilter field:=12, Criteria1:="Open"
    Sheets("Details").UsedRange.AutoFilter field:=1, Criteria1:=Goal
    Sheets("Details").Activate
    
    Cancel = True

ElseIf Not Intersect(Target, Range("M7:M166")) Is Nothing Then
    
    On Error Resume Next
    Sheets("Details").ShowAllData

    Sheets("Details").UsedRange.AutoFilter field:=1, Criteria1:=Goal
    Sheets("Details").Activate
    
    Cancel = True
 
ElseIf etc.etc.

End If

ErrorRoutine:
Application.EnableEvents = True

End Sub

The ElseIf chains just go on. It works but there's a big issue I just can't debug that I think has something to do with how I clear the filters with .ShowAllData between each If/Then.

For some reason whenever a filter returns 0 values, it won't properly reset to "ShowAllData" when you trigger another double click event that comes after it in the code. So that event's filter will not return any values even if there definitely are.

Anything weird in my code?

CodePudding user response：

There's no need to turn off events here, since the actions you're taking won't re-trigger the same event code.

To streamline the code, I'd extract the filter logic to a stand-alone method, and call that from the event handler with the appropriate parameters

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

    Dim Goal As String, col As String
    
    If Target.Row < 7 Or Target.Row > 166 Then Exit Sub
    
    Goal = "*" & Me.Cells(Target.Row, 7).Value & "*"
    'Target column letter
    col = Replace(Target.EntireColumn.Cells(1).Address(False, False), "1", "")
    Cancel = True 'assuming a column match below
    Select Case True
        Case col = "L":
            FilterDetails Array(12, "Open", 1, Goal)
        Case col = "M":
            FilterDetails Array(1, Goal)
        Case Not Application.Intersect(Target, Me.Range("AO7:CA7")) Is Nothing:
            'filter based on specific cell in AO7:CA7?
                
        'add other cases
        
        Case Else:
            Cancel = False 'no match, so reset Cancel
    End Select
    
End Sub


'Filter Details using one or more criteria passed in `arrSettings`
'  (as pairs of column number/filter value)
Sub FilterDetails(arrSettings)
    Dim i As Long
    With ThisWorkbook.Worksheets("Details")
        If .AutoFilterMode Then .AutoFilter.ShowAllData
        'or try this
        On Error Resume Next 'ignoring any error
        .ShowAllData
        On Error Goto 0      'stop ignoring errors
        For i = LBound(arrSettings) To UBound(arrSettings) - 1 Step 2
            .UsedRange.AutoFilter field:=arrSettings(i), Criteria1:=arrSettings(i   1)
        Next i
        .Activate
    End With
End Sub