Event handler

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