How to Filter Another Excel Sheet by Double-Clicking a Cell
Question details
The user wants to trigger a macro by double-clicking a specific review code cell to automatically filter and display matching records on a different worksheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Navigating between related datasets, such as jumping from a summary sheet to a detailed data sheet based on a specific cell value.
- Observed behavior
- The user needs an automated process to cancel the default double-click editing behavior, apply an AutoFilter to a target sheet using the clicked cell's value, and activate the target sheet instantly.
Ensure that your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled macros in your Trust Center or security settings.
Use the Worksheet_BeforeDoubleClick VBA Event
This method uses a built-in Excel event handler to detect a double-click on a specific column, applies the AutoFilter to another sheet, and automatically navigates you there.
The Worksheet_BeforeDoubleClick event is triggered instantly when a user double-clicks any cell in a designated worksheet. By targeting a specific column (like the Review Code column) and canceling the default cell-editing action, you can capture the cell's value and pass it to an AutoFilter command on your target sheet.
Press ALT + F11 on your keyboard to launch the Microsoft Visual Basic for Applications window.
In the Project Explorer pane on the left, find your workbook and double-click the specific sheet where you will be clicking the cells (e.g., 'Sheet1 (Production Plan)').
From the top-left dropdown in the code window, select 'Worksheet', and from the right dropdown, select 'BeforeDoubleClick'. Write code to check if 'Target.Column' equals your desired column number.
Add 'Cancel = True' to stop cell editing. Then, write the command to apply the AutoFilter on the target sheet (e.g., 'Worksheets("Delivery Plan").Range("A1:Z100").AutoFilter Field:=1, Criteria1:=Target.Value') and finish with 'Worksheets("Delivery Plan").Activate'.
Close the VBA editor and double-click a valid cell in your source sheet to ensure it properly filters and switches to the target sheet.
Automate Your Workflow with WPS Spreadsheet Macros
WPS Spreadsheet provides powerful data management capabilities, including robust AutoFilter functions and excellent compatibility with Microsoft Excel VBA macros. You can easily automate cross-sheet filtering without modifying your existing code.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your Macro-Enabled Workbook (.xlsm).
- 2. Access the Developer Tab: Navigate to the Developer tab on the ribbon and click on 'VBA Editor' to view your project.
- 3. Insert the Event Macro: Paste your 'Worksheet_BeforeDoubleClick' macro code into the specific source sheet module.
- 4. Double-Click to Filter: Return to your spreadsheet and double-click the target cell to test the automatic cross-sheet filtering.
Frequently Asked Questions
Why isn't my double-click macro working?
Ensure macros are enabled in your security settings. Also, verify that the code is placed inside the specific Worksheet module where the click happens, rather than in a standard Module (like Module1).
How do I clear the filter applied by the macro?
You can manually clear it by going to the target sheet, clicking the Data tab, and selecting 'Clear'. To clear it automatically via VBA, you can run 'ActiveSheet.ShowAllData'.
Can I trigger this macro from multiple columns?
Yes. In your VBA code, you can use the 'Intersect' method or an 'If' statement with an 'Or' condition (e.g., 'If Target.Column = 1 Or Target.Column = 3 Then') to allow double-clicks in multiple specific columns.
Will this code work in WPS Office?
Yes, WPS Office supports VBA macros in its professional or advanced versions. As long as the VBA module is installed and macros are enabled, standard Excel VBA events like BeforeDoubleClick will function properly.
