Multiple Double Click Events on Different Ranges in 1 Worksheet

[karenzh]

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

My different double click events look like this:

1.

If Not Intersect(Target, Range("L7:L166")) Is Nothing Then
    Dim Goal As String
    Goal = "*" & Cells(Target.Row, 7) & "*"
    
    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
End If

2.

If Not Intersect(Target, Range("M7:M166")) Is Nothing Then
    Dim Goal As String
    Goal = "*" & Cells(Target.Row, 7) & "*"

    On Error Resume Next
    Sheets("Details").ShowAllData

    Sheets("Details").UsedRange.AutoFilter field:=1, Criteria1:=Goal
    Sheets("Details").Activate
End If

I'll have a lot of them. Ballpark ~20 ish on this one sheet. Does anyone have a method for doing this?

I have it semi working linked together using a solution like this: BeforeDoubleClick Event with multiple ranges
However the main issue with this method is that the filter doesn't reset properly between each doubleclick event. For reasons I'm unsure of, when a filter returns 0 values, it won't properly reset to "ShowAllData" when you do another double click event so that filter will not return values even if there are.

 

[kevin9999]

I'm not 100% clear on what it is you want - in both 1 & 2 above you're using the value (with wildcards) in the Target row - column G - as the filter criteria. Is it the case that you simply want to show all data if nothing is returned by the filter? If so, the following code should work for you. I'm also not sure what you mean by this:

I'll have a lot of them. Ballpark ~20 ish on this one sheet.

Do you mean that you have a different If statement depending on which column is the Target? As I said, in both cases above (clicking on columns L & M) you're still using column G values as the filter criteria.

Anyhow, see if this works for what you want (simply expand the range L6:M166 to whatever your total columns are).

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, cancel As Boolean)
    If Not Intersect(Me.Range("L6:M166"), Target) Is Nothing Then
        cancel = True
        Dim Goal As String, ws As Worksheet
        Set ws = Worksheets("Details")
        
        Goal = "*" & Cells(Target.Row, 7) & "*"
        With ws.UsedRange
            .AutoFilter 12, "Open"
            .AutoFilter 1, Goal
        End With
        If Application.CountA(ws.Range("A:A").SpecialCells(12)) = 1 Then ws.ShowAllData
        Application.Goto ws.Range("A1"), 1
    End If
End Sub