Double Click to Filter Multiple Columns

[alexdowd2]

I have 2 sheets, one is a library of sorts (Sheet1) that has an Id column and then corresponding filter values for columns on Sheet2. That Id column from Sheet1 is NOT on Sheet2. Conceptually, what I want to be able to do is click on the Id column on Sheet1, then filter Sheet2 based on the values in that Id's row, which correspond to values in columns on Sheet2 (the column header names are the same). For example, sample data may look like this:

Sheet1

Id	Color	Make	Model
1​	Blue	Honda	Civic
2​	Gray	Ford	Taurus
3​	Green	Honda	Insight
4​	Green	Tesla	Model S

Sheet2

Color	Make	Model
Blue	Honda	Civic
Gray	Ford	Taurus
Green	Honda	Insight
Green	Tesla	Model S

Use Case: I double click 1 on Sheet1 and that filters Sheet2 on all records where Color = Blue, Make = Honda, Model = Civic.

 

[alexdowd2]

Once you have opened the immediate window, leave it open & then double-click in col A

Oh i think i might have a different version of excel, Ctrl +G doesn't bring up the immediate window. nothing happens in the immediate window when i double click

 

[Fluff]

What happens if you click on View in the VB Editor & select immediate window?

 

[alexdowd2]

What happens if you click on View in the VB Editor & select immediate window?

Nothing, I have the Immediate window pinned at the bottom

 

[Fluff]

In that case it sounds as though the code is not running. Try closing Excel & re-opening it.

 

[alexdowd2]

In that case it sounds as though the code is not running. Try closing Excel & re-opening it.

I've done that multiple times now
The code is definitely running, it navigates (i added a Sheet.Activate line to go to the sheet) to the correct sheet and selects the correct columns (i can tell because the filter icon shows filters are applied) except instead of selecting the values it's clearing them.

 

[Fluff]

Just realised that I never posted the modified code.
Use

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Target.Column = 1 Then
      Cancel = True
      If Target.Value = "" Then Exit Sub
      Debug.Print "|" & Target.Offset(, 1).Value & "|", "|" & Target.Offset(, 2).Value & "|"
      With Sheets("Sheet2").Range("A1")
         .AutoFilter 1, Target.Offset(, 1)
         .AutoFilter 2, Target.Offset(, 2)
         .AutoFilter 3, Target.Offset(, 3)
      End With
   End If
End Sub

 

[alexdowd2]

Just realised that I never posted the modified code.
Use

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Target.Column = 1 Then
      Cancel = True
      If Target.Value = "" Then Exit Sub
      Debug.Print "|" & Target.Offset(, 1).Value & "|", "|" & Target.Offset(, 2).Value & "|"
      With Sheets("Sheet2").Range("A1")
         .AutoFilter 1, Target.Offset(, 1)
         .AutoFilter 2, Target.Offset(, 2)
         .AutoFilter 3, Target.Offset(, 3)
      End With
   End If
End Sub

Sorry I just got back to this thread. It still doesn't work. Here is a screenshot of what I'm seeing along with the real code. I have all the other filters commented out just for testing.


The selection I am making sure return the filter "Less Than 4 Hours"

 

[Fluff]

When you run that, what does it say in the immediate window?

 

[alexdowd2]

When you run that, what does it say in the immediate window?

|CRITICAL| |Less than 4 hours|

for context, Critical is the value in the first offset column

 

[alexdowd2]

|CRITICAL| |Less than 4 hours|

for context, Critical is the value in the first offset column

It also says those 2 things for all the filters even though there are definitely more values (verified manually)