How can you control what happens when you double-click a Pivot Table Entry in Excel

Question

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?

Answer 1

Try this.

LOGIC:

1. Identify your pivot range
2. In the Worksheet_BeforeDoubleClick event check if the user clicked in the Pivot
3. Cancel the double click

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: