[SOLVED] Double click on a cell shows other sheet filter with cell content

Double click on a cell shows other sheet filter with cell content

I am working on a intuitive BOM. In my sheet1 I have the complete list of parts used into assemblies (C contains all references) In my sheet2 I have the list of assemblies done with all parts. Column A are the references of assemblies, then G2:X10000 (table can increase) contains all part references (each row is the BOM of an assembly)

I want to double click on a part reference in sheet 2 (G2:X10000) and to switch automatically to sheet1 with as filter for column C the reference of double clicked cell.

I tried some codes already but always returning errors even adapting to my case.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        If ActiveCell.Column = 1 Then
        Feuil1.ListObjects("Components_table").Range.AutoFilter Field:=3, Criteria1:=ActiveCell.Value.
    Feuil1.Activate
        End If
    End Sub

Here Feuil1 is my sheet1 with "Components_table" a name I gave to the component table (apparently it was needed with this code). Here I tried the code only with double click activated on a test sheet in first column before placing the code in my sheet2. There is always an error with the line "Feuil1.ListObjects....", as is I have "execution error 9" message.

Could you help me finding a specific code for my application?

Thank you

Solution

This should work.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column = 1 Then
        Feuil1.ListObjects("Components_table").Range.AutoFilter Field:=3, Criteria1:=Target.Value
        Feuil1.Activate
        Cancel = True
    End If
End Sub