vba 在新工作表中双击单元格显示过滤器时创建宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13284076/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create macro upon double click cell display filter in new sheet
提问by user1808404
I have a question regarding creating macros whereas the scenarios as follows:
我有一个关于创建宏的问题，而场景如下：
Sheet1 Upon clicking any cell in Sheet1, it will automatically filter based on cell A and B.
Sheet1 单击 Sheet1 中的任何单元格后，它将根据单元格 A 和 B 自动过滤。
Sheet2 Automatically display filtered criteria based on double click from Sheet1
Sheet2 根据来自 Sheet1 的双击自动显示过滤条件
For example: when I double click on C1, on Sheet2 will automatically diplay filtered data based on A1 and B1 and same thing goes to if I double clik on C2 on Sheet2 will automatically diplay filtered data based on A1 and B2.
例如：当我双击 C1 时，Sheet2 上会自动显示基于 A1 和 B1 的过滤数据，如果我双击 Sheet2 上的 C2 也会自动显示基于 A1 和 B2 的过滤数据。
Really need help from the experts here.
真的需要这里的专家的帮助。
回答by Jook
This would be the code you need to catch your single-click event:
这将是您捕获单击事件所需的代码：
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Debug.Print Target.Address
End Sub
This would be the code you need to catch your double-click event:
这将是您捕获双击事件所需的代码：
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Debug.Print Target.Address
'cancel the double click, prohibiting editng of cell per double-click
Cancel = true
End Sub
I would have helped you with your filtering too, but since you did not paste any code to that, and I don't get how excactly you want what data to be filtered, I'll leave that up to you ;)
我也会帮助您进行过滤，但是由于您没有粘贴任何代码，而且我不知道您希望过滤哪些数据，我将把它留给您；）
Edit:
编辑：
This code can be used for Worksheet_SelectionChangeand will set a filter based on a valid selection inside the used range. If a filter is already in place, it will be deactivated.
此代码可用于Worksheet_SelectionChange并根据使用范围内的有效选择设置过滤器。如果过滤器已经就位，它将被停用。
On Error Resume Next
If Sheet1.AutoFilterMode Then
'clear existing autofilter
Sheet1.UsedRange.AutoFilter
Else
'setup filter based on selection
Sheet1.UsedRange.AutoFilter field:=Target.Column, _
Operator:=xlFilterValues, _
Criteria1:=Target.Value, _
VisibleDropDown:=True
End If
