Event handler

[VBA Noob]

Hi,

Looking for excel to activate a macro when a user double clicks on a
pivot table to drill down into what makes up the total. Think I need
something like the beliow to call my macro called FillColours.

Does anyone have any thoughts ??


VBA:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal
Target As Range, _
Cancel As Boolean)


If ActiveSheet.PivotTables.EnableDrilldown = True Then Call
FillColours

End Sub

Thanks
VBA Noob

 

[Guest]

Yes, you are on the right track except the code as you have it would run for
any double click on the sheet, whether on the pivottable or no. To make it
run only if the user double-clicks on the PivotTable, first it would be
easier to put the code in the Worksheet_BeforeDoubleClick event so you
already are sure you are on the right sheet, then check to see if the cell is
in the pivottable range - you do that by using the Intersect method to see if
the Target range intersects (is part of) the pivottable's range which is
either the TableRange1 or TableRange2 property (depends on if you want to
include the field names). Also, one note: you can always replace IF A=True
THEN by IF A THEN; it simplifies the code a bit:

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

 

[Guest]

Enabledrilldown is a property of an individual pivot table, so you can't use
it with the pivottables collection as you show. You would probably first
want to check if the Target is within a range that would drilldown. then
check for that pivot table if the enable drilldown property is enabled.

 

[VBA Noob]

Hi K Dale,

Thanks for this code however I get a error message for Is nothing.

Thanks

Danny

Yes, you are on the right track except the code as you have it would ru
for
any double click on the sheet, whether on the pivottable or no. T
make it
run only if the user double-clicks on the PivotTable, first it woul
be
easier to put the code in the Worksheet_BeforeDoubleClick event so you
already are sure you are on the right sheet, then check to see if th
cell is
in the pivottable range - you do that by using the Intersect method t
see if
the Target range intersects (is part of) the pivottable's range whic
is
either the TableRange1 or TableRange2 property (depends on if you wan
to
include the field names). Also, one note: you can always replace I
A=True
THEN by IF A THEN; it simplifies the code a bit:

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

 

[Guest]

Just as I said before, this will give you an error:

ActiveSheet.PivotTables.EnableDrilldown

but

? activesheet.PivotTables(1).enabledrilldown
True

works fine as you can see.