|
Event handler
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 |
Event handler
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 |
Event handler
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 |
Event handler
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 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 -- - 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 Noo ----------------------------------------------------------------------- VBA Noob's Profile: http://www.excelforum.com/member.php...fo&userid=3383 View this thread: http://www.excelforum.com/showthread.php?threadid=53632 |
Event handler
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 |
| All times are GMT +1. The time now is 08:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com