vba 如何控制在 Excel 中双击数据透视表条目时发生的情况
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12526638/
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
How can you control what happens when you double-click a Pivot Table Entry in Excel
提问by aSystemOverload
Is it possible to intercept the double-click on a pivot table, prevent it from showing the underlying data, so that you can provide a structured screen as you wish the viewer to see it?
是否可以拦截对数据透视表的双击，阻止它显示底层数据，以便您可以提供您希望查看者看到的结构化屏幕？
回答by Siddharth Rout
Try this.
尝试这个。
LOGIC:
逻辑：
- Identify your pivot range
- In the
Worksheet_BeforeDoubleClickevent check if the user clicked in the Pivot - Cancel the double click
- 确定您的枢轴范围
- 在
Worksheet_BeforeDoubleClick事件中检查用户是否单击了 Pivot - 取消双击
CODE: This code goes in the worksheet code area which has the pivot (See screenshot)
代码：此代码位于具有枢轴的工作表代码区域（见截图）
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim rng As Range
'~~> This is your pivot table range. Change as applicable
Set rng = Range("A1:D10")
'~~> Check if the double click happend in the Pivot
If Not Intersect(Target, rng) Is Nothing Then
'~~> Cancel Double click
Cancel = True
End If
End Sub
SCREENSHOT:
屏幕截图：
回答by Raj
You can try doing something similar to the code below. This creates a Range, WorkSheet and PivotTable:
您可以尝试执行类似于以下代码的操作。这将创建一个范围、工作表和数据透视表：
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim rng As Range
Dim wks As Worksheet
Dim pt As PivotTable
Set wks = Target.Worksheet
For Each pt In wks.PivotTables()
Set rng = Range(pt.TableRange1.Address)
If Not Intersect(Target, rng) Is Nothing Then
Cancel = True
End If
Next
End Sub
回答by Kevin Pope
This is entirely stolen frombased on Siddharth Rout's answer, but will be more flexible, as it will look through all Pivot Tables on a sheet and cancel if you double click in any of them :
这完全是从Siddharth Rout 的回答中窃取的，但会更灵活，因为它会查看工作表上的所有数据透视表，并在双击其中任何一个时取消：
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim rng As Range
Dim wks As Worksheet
Dim pt As PivotTable
Set wks = Target.Worksheet
For Each pt In wks.PivotTables()
Set rng = Range(pt.TableRange1.Address)
If Not Intersect(Target, rng) Is Nothing Then
Cancel = True
End If
Next
End Sub
