Double Click on a cell to filter data

[aganatra19]

Hi there, I am new to this forum and I have a sheet where I would like to filter the information on the second worksheet based on the ID in worksheet 1.
Worksheet 1=

Worksheet 2

what I would like is, when you click on cell P2, it will look at what the Risk ID is in Cell A2 and filter the information from Worksheet 2. however, there are some Risk ID's that will have more than 1 risk ID on it, but i would still like it to show those items too. like if Cell A2 in worksheet 1 has Risk ID of XC19. worksheet 2 will filter the data to show all items that have XC19 (B7,B8,B9,B10).

In addition, as you can see, there is a narrative column too, and new columns will be added after that, based on the month. can the filter be dynamic to ensure it can keep search to the last column and filter it based on that month?

I'd appreciate it if you could also walk me through the code, for future reference to understand how it works, please

Thanks again

A

 

[kevin9999]

This might take a few goes to get it to just where you want it, but here’s a starting point.

For future reference, you will get a better/faster response if you provide a sample of (both) sheets using Mr Excel’s excellent XL2BB add in. People trying to help you can’t copy data from an image, and would be reluctant to type all that info into a blank sheet to test their code.

The following code assumes your worksheet 1 is called “Sheet1” and your worksheet 2 is called “Sheet2” – change these names to suit if necessary. You need to place this code in the worksheet code area of “Sheet1”. If you’re not sure where to put this code, right-click on the "Sheet1" tab, select view code, put the code in the pane that appears on the right. Save the file as a macro-enabled workbook & test.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, cancel As Boolean)

    On Error GoTo Escape                '<~~ in case something goes wrong code jumps to "Escape" line
    Application.EnableEvents = False    '<~~ turn events off for safety
    cancel = True                       '<~~ to switch off the edit function of a double click
  
    'Only execute the code if the double-click occurs in column P in the range P2 down to last used cell in P
    If Not Intersect(Range("P2", Cells(Rows.Count, "P").End(xlUp)), Target) Is Nothing Then
        'Declare a string variable for the filter value
        Dim s As String
        'Get the filter value from column A (=1) in the target row
        s = Cells(Target.Row, 1)
      
        'Set the filter on Sheet2
        With Worksheets("Sheet2").Cells(1).CurrentRegion
            .AutoFilter 2, "*" & s & "*"    '<~~ use the wildcard "*" to pick up any occurrence of the value in the cell
            Application.Goto .Range("A1")   '<~~ go to cell A1 in "Sheet2 after the filter has been applied
        End With
    End If
  
Continue:
    Application.EnableEvents = True '<~~ turn events back on
    Exit Sub                        '<~~ sub ends here
Escape:                             '<~~ this is where the code goes if an error happens
    MsgBox "Error " & Err.Number & ": " & Err.Description   '<~~ provides info if/when an error happens
    Resume Continue

End Sub

 

[aganatra19]

This might take a few goes to get it to just where you want it, but here’s a starting point.

For future reference, you will get a better/faster response if you provide a sample of (both) sheets using Mr Excel’s excellent XL2BB add in. People trying to help you can’t copy data from an image, and would be reluctant to type all that info into a blank sheet to test their code.

The following code assumes your worksheet 1 is called “Sheet1” and your worksheet 2 is called “Sheet2” – change these names to suit if necessary. You need to place this code in the worksheet code area of “Sheet1”. If you’re not sure where to put this code, right-click on the "Sheet1" tab, select view code, put the code in the pane that appears on the right. Save the file as a macro-enabled workbook & test.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, cancel As Boolean)

    On Error GoTo Escape                '<~~ in case something goes wrong code jumps to "Escape" line
    Application.EnableEvents = False    '<~~ turn events off for safety
    cancel = True                       '<~~ to switch off the edit function of a double click
 
    'Only execute the code if the double-click occurs in column P in the range P2 down to last used cell in P
    If Not Intersect(Range("P2", Cells(Rows.Count, "P").End(xlUp)), Target) Is Nothing Then
        'Declare a string variable for the filter value
        Dim s As String
        'Get the filter value from column A (=1) in the target row
        s = Cells(Target.Row, 1)
     
        'Set the filter on Sheet2
        With Worksheets("Sheet2").Cells(1).CurrentRegion
            .AutoFilter 2, "*" & s & "*"    '<~~ use the wildcard "*" to pick up any occurrence of the value in the cell
            Application.Goto .Range("A1")   '<~~ go to cell A1 in "Sheet2 after the filter has been applied
        End With
    End If
 
Continue:
    Application.EnableEvents = True '<~~ turn events back on
    Exit Sub                        '<~~ sub ends here
Escape:                             '<~~ this is where the code goes if an error happens
    MsgBox "Error " & Err.Number & ": " & Err.Description   '<~~ provides info if/when an error happens
    Resume Continue

End Sub

Thanks Kevin.

I've tried the code you posted and it seems to work fine so far. Really appreciate the help on this. I'll try to use the add-in provided next time for ease.

Once again, thank you

 

[kevin9999]

Thanks Kevin.

I've tried the code you posted and it seems to work fine so far. Really appreciate the help on this. I'll try to use the add-in provided next time for ease.

Once again, thank you

Glad it worked for you. Just let me know if you encounter any problems, and welcome to the Board

 

[aganatra19]

Glad it worked for you. Just let me know if you encounter any problems, and welcome to the Board

Hey Kevin,

I’ve encountered an issue. It’s only a minor one. But when you double click on other cells to input something, I think the double click code has disabled it everywhere else. I know you can just use F2 to get into edit mode for that cell. But is there a code I would need to add on there where the double click entry is only for column “P” and it doesn’t affect double clicking to edit any other cell on that same worksheet?

Many thanks

 

[kevin9999]

Sorry, just saw this. Try moving the cancel = True line, like this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, cancel As Boolean)

    On Error GoTo Escape                '<~~ in case something goes wrong code jumps to "Escape" line
    Application.EnableEvents = False    '<~~ turn events off for safety
      
    'Only execute the code if the double-click occurs in column P in the range P2 down to last used cell in P
    If Not Intersect(Range("P2", Cells(Rows.Count, "P").End(xlUp)), Target) Is Nothing Then
        cancel = True                       '<~~ MOVE THIS TO HERE ***
        'Declare a string variable for the filter value
        Dim s As String
        'Get the filter value from column A (=1) in the target row
        s = Cells(Target.Row, 1)
      
        'Set the filter on Sheet2
        With Worksheets("Sheet2").Cells(1).CurrentRegion
            .AutoFilter 2, "*" & s & "*"    '<~~ use the wildcard "*" to pick up any occurrence of the value in the cell
            Application.Goto .Range("A1")   '<~~ go to cell A1 in "Sheet2 after the filter has been applied
        End With
    End If
    
Continue:
    Application.EnableEvents = True '<~~ turn events back on
    Exit Sub                        '<~~ sub ends here
Escape:                             '<~~ this is where the code goes if an error happens
    MsgBox "Error " & Err.Number & ": " & Err.Description   '<~~ provides info if/when an error happens
    Resume Continue

End Sub