|
Subject:
|
Pivot Table Update on DropButtonClick
|
|
Posted By:
|
Wulffman
|
Post Date:
|
2/21/2006 12:17:34 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").PivotFields("ITDept").CurrentPage = a
a = Worksheets("BuildQry").Cells(8, 1)
Sheets("BuildQry").PivotTables("PivotTable4").PivotFields("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
|
|
