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

sadonnie

New Member
Joined
Dec 17, 2018
Messages
11
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
1641920074289.png


ServerList Worksheet
1641920024686.png


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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
11,726
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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.
VBA Code:
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

New Member
Joined
Dec 17, 2018
Messages
11
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

Well-known Member
Joined
Apr 11, 2012
Messages
11,726
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
You are very welcome. :)
 

sadonnie

New Member
Joined
Dec 17, 2018
Messages
11

ADVERTISEMENT

@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

Well-known Member
Joined
Apr 11, 2012
Messages
11,726
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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.
VBA Code:
If Intersect(Target, Range("G:H")) Is Nothing Then Exit Sub
 

sadonnie

New Member
Joined
Dec 17, 2018
Messages
11

ADVERTISEMENT

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.
VBA Code:
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

Well-known Member
Joined
Apr 11, 2012
Messages
11,726
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
No apologies necessary. :)
 

Muthuv19

New Member
Joined
Feb 13, 2022
Messages
2
Office Version
  1. 365
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

Well-known Member
Joined
Apr 11, 2012
Messages
11,726
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,131
Messages
5,835,579
Members
430,368
Latest member
User800

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top