Pivot Table Update on DropButtonClick

Wulffman · February 21st, 2006, 01:17 AM

I am struggling with automagically updating a Pivot Table's fields based upon user selection in a drop box.

I've read the help file and it indicates that the event that I want to use is the object_dropbuttonclick() event.

In trying to implement it, I have the following code in a module in Excel 2k3:

Private Sub Worksheet_DropButtonClick()
    a = Worksheets("BuildQry").Cells(6, 1)
    Sheets("BuildQry").PivotTables("PivotTable2").Pivo tFields("ITDept").CurrentPage = a
    a = Worksheets("BuildQry").Cells(8, 1)
    Sheets("BuildQry").PivotTables("PivotTable4").Pivo tFields("ITName").CurrentPage = a
End Sub

It doesn't seem to matter where I locate the code (in a module, a workbook, or a sheet).

In order to get other cell's drop down list contents to dynamically change (driven from by pivot tables based upon the contents of an initially selected list box), I have to call the macro with a button after I select an item in the first cell's list.

I would like the code to execute as soon as I make a selection from the list, or 'navigate' off of the cell with the validation list.

Please review and advise the errors in my attempts. TIA.

-t

Dean45ess · February 5th, 2015, 12:36 PM

For anyone else who may want to do this:

I use an intersect event so that when the field is modified, it automatically calls a routine to update the field in the pivot table. The intersect event is on the Worksheet where the pivot table is located.

Code:

 
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("MonthTr")) Is Nothing Then
    Run "StateSales"
Else
    Exit Sub
End If
                
End Sub

Sub StateSales()
With ActiveSheet.PivotTables("PvtMonthTax").PivotFields("Month")
     .ClearAllFilters
     .PivotFilters.Add Type:=xlCaptionEquals, Value1:=ActiveSheet.Range("MonthTr").Value

     ActiveSheet.PivotTables("PvtMonthTax").PivotCache.Refresh
        
End With