Event handler

**VBA Noob** · 04-26-2006, 06:22 AM

Posted: Thu Apr 27, 2006 7:23 pm Post subject: Example of a Event Handlers for pivot Tables

--------------------------------------------------------------------------------

Hi all,

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

Code:

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

NoPT:
On Error GoTo 0

End Sub

Thanks

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

K Dales · 04-26-2006, 08:10 AM

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

--
- K Dales

"VBA Noob" wrote:

>
> 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
>
>
> --
> VBA Noob
> ------------------------------------------------------------------------
> VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
> View this thread: http://www.excelforum.com/showthread...hreadid=536327
>
>

Tom Ogilvy · 04-26-2006, 08:15 AM

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.

--
Regards,
Tom Ogilvy

"VBA Noob" wrote:

>
> 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
>
>
> --
> VBA Noob
> ------------------------------------------------------------------------
> VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
> View this thread: http://www.excelforum.com/showthread...hreadid=536327
>
>

**VBA Noob** · 04-26-2006, 09:32 AM

Hi K Dale,

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

Thanks

Danny

Originally Posted by K Dales

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

--
- K Dales

"VBA Noob" wrote:

>
> 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
>
>
> --
> VBA Noob
> ------------------------------------------------------------------------
> VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
> View this thread: http://www.excelforum.com/showthread...hreadid=536327
>
>

Tom Ogilvy · 04-26-2006, 10:55 AM

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.

--
Regards,
Tom Ogilvy

"VBA Noob" wrote:

>
> Hi K Dale,
>
> Thanks for this code however I get a error message for Is nothing.
>
> Thanks
>
> Danny
>
> K Dales Wrote:
> > 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
> >
> >
> > --
> > - K Dales
> >
> >
> > "VBA Noob" wrote:
> >
> > >
> > > 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
> > >
> > >
> > > --
> > > VBA Noob
> > >
> > ------------------------------------------------------------------------
> > > VBA Noob's Profile:
> > http://www.excelforum.com/member.php...o&userid=33833
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=536327
> > >
> > >
>
>
> --
> VBA Noob
> ------------------------------------------------------------------------
> VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
> View this thread: http://www.excelforum.com/showthread...hreadid=536327
>
>