Double-click in cell in one Excel sheet auto-filters another sheet based on multiple filter criteria

[sadonnie]

I have read through the other posts and some reference using the Hyperlink Event and some use the BeforeDoubleClick event. I am open, just stuck on how to best approach the VBA code.

Scenario:
I have 1 workbook with 2 worksheets:
1. Worksheet 1 name is Wave
2. Worksheet 2 name is ServerList

Screenshots of Worksheets:

Wave Worksheet

ServerList Worksheet

Objective:
As a user, I want to go to the WAVE worksheet and click on a # value in either column 7 or column 8 (Production or Non-Production) in order to auto-filter the ServerList Worksheet to display those specific servers.

Example: (clicking the #7 under the Production List column)
In the WAVE worksheet, when I click on the #7 under the Production list column (in row 2 under column 7) it will filter the Serverlist worksheet to show me those specific 7 servers. The criteria I need help coding is when I click on the # 7, I need to capture the APP ID value in Column 4 of the WAVE worksheet and the that I clicked the #7 in the PRODUCTION column. I need the word "Production". I don't care if Production/Non-Production is hard-coded since that will never change as a value.

So using the app id value (in column 4) and knowing I clicked on the value under the PRODUCTION column (column 7) I would have the code auto-filter the ServerList worksheet with the criteria AppID = 228 and Environment = Production. That would show me the specific 7 servers.

Conversely, If I clicked the #4 in Column 8 (row 3), it would auto-filter the ServerList worksheet with the criteria AppID = 229 and Environment = Non-Production. That would show me the specific 4 servers.

I have been stuck trying to use either DoubleClick vs Hyperlink and getting the correct values to pass to the auto-filter.

Any help is greatly appreciated from the community here!

Thanks

 

[mumps]

Untested.
Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "Wave" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Double click on the cell.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("G:H")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Select Case Target.Column
        Case Is = 7
            With Sheets("ServerList").Range("A1").CurrentRegion
                .AutoFilter 4, Target.Offset(, -3).Value
                .AutoFilter 10, Cells(1, 7).Value
            End With
        Case Is = 8
            With Sheets("ServerList").Range("A1").CurrentRegion
                .AutoFilter 4, Target.Offset(, -4).Value
                .AutoFilter 10, Cells(1, 8).Value
            End With
    End Select
    Sheets("ServerList").Activate
    Application.ScreenUpdating = True
End Sub

 

[sadonnie]

Thank you @mumps this worked! I was headed down the Select Case path, but you obliviously solved it for me in a much more efficient way.

Much appreciated for your time and input!

 

[mumps]

You are very welcome.

 

[sadonnie]

@mumps, just a quick follow-on question. Is there a way to only "fire off" this DoubleClick event by telling it to only call the DoubleClick event if the user double clicks only in column G and H (anywhere else it does not call the event)?

 

[mumps]

This line of code restricts the double click to columns G and H. The macro should not run if you double click in any other column.

If Intersect(Target, Range("G:H")) Is Nothing Then Exit Sub

 

[sadonnie]

This line of code restricts the double click to columns G and H. The macro should not run if you double click in any other column.

If Intersect(Target, Range("G:H")) Is Nothing Then Exit Sub

DOH! I figured that but had not double clicked anywhere else. My apologies for my question.

 

[mumps]

No apologies necessary.

 

[Muthuv19]

Please help me with this i have 8 Columns to Filter from a dashboard with Rows ranges from D1 TO D7 each cell will filter different columns and Different criteria in source data, can you help me with it

 

[mumps]

Hi and welcome to the Forum. Please start your own new thread. Use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). Include a link to this thread if you feel it is helpful.