Event Handlers for pivot

Hi,

I'm got a pivot table in a sheet called "Pivot" which when the user drills down into the pivot by double clicking it opens up a new sheet with the data. I wanted the event handler to run a macro after the users double click. Got the below code but not working.

Any thoughts ??

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)


If ActiveSheet.PivotTables.EnableDrilldown and _
Not(Intersect(Target, ActiveSheet.PivotTables(1).TableRange2) Is
Nothing) _
Then Call FillColours


End Sub

Thanks in advance

VBA Noob

Re: Event Handlers for pivot example

Hi all,

Think i've cracked it. My macro is called Fillcolors

Option Explicit
Public mSheet As String
 
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, _
  Cancel As Boolean)
 
  Dim curCell As String, ptname As String, a As Integer
 
Start:
  If ActiveSheet.PivotTables.Count = 0 Then GoTo NoPT
 
  On Error GoTo NoPT
  If IsEmpty(Target) And ActiveCell.PivotField.Name <> "" Then
    Cancel = True
    GoTo NoPT
  End If
  mSheet = ActiveSheet.Name
  curCell = ActiveCell.Address
  ptname = Sh.Range(curCell).PivotTable
 
  If ActiveSheet.PivotTables(ptname).EnableDrilldown Then
    Selection.ShowDetail = True
    Call FillColors
    mSheet = ActiveSheet.Name
    End If
 
NoPT:
  On Error GoTo 0
 
End Sub

Quote from VBA Noob

Hi,

I'm got a pivot table in a sheet called "Pivot" which when the user drills down into the pivot by double clicking it opens up a new sheet with the data. I wanted the event handler to run a macro after the users double click. Got the below code but not working.

Any thoughts ??

Code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)


If ActiveSheet.PivotTables.EnableDrilldown and _
Not(Intersect(Target, ActiveSheet.PivotTables(1).TableRange2) Is
Nothing) _
Then Call FillColours


End Sub

Display More

Thanks in advance

VBA Noob

Display More