Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Pivot Tables, Page Fields, Visible PivotItems, Activation
Hi All,
I'm having two problems. 1. Excel PivotTables have PivotFields. PivotFields may be in a Row, Column or Page Orientation. In the Page Orientation, the meaning of VisibleItems or HiddenItems seems odd. Say I have one PageField and this has two PivotItems. If the PageField shows "(all)", then the two PivotItems are considered to be Hidden or Non Visible. Additionally, I can right click on the pop up context menu, choose, Field Properties and nominate that one of the PivotItems should not contribute to the overall stats. Let's for example say the possible entries are "Male", "Female", "(all)". I choose to exclude "Male" My question is, how do I programmatically identify that I have excluded one PivotItem? The VisibleItems.Count or .Visible=False or HiddenItems.Count does not yield what I expect. 2. I had a PivotTable on a Worksheet. Going to VBA, it had a WorksheetPivotTable_Change event. I saw it activating. But after removing and placing a new PivotTable on the same Worksheet, the WorksheetPivotTable event does not trigger anymore and in fact the VBE does not think it is there. How do I work with this? Thanks in advance for any help. Ananda |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Pivot Tables, Page Fields, Visible PivotItems, Activation
Hi Sebastien,
"sebastienm" wrote in message ... Function IsIncluded(PItem As PivotItem) As Boolean Dim pi As PivotItem On Error Resume Next Set pi = PItem.Parent.VisibleItems(PItem.Name) IsIncluded = (Err.Number = 0) End Function That's really cool and thanks for wrapping it nicely up in a function for me. I would not have thought of that approach at all. 2. WorksheetPivotTable_Change I have xl2000 and this event does not exists, so i guess you have either xlXP or xl2003. That is it's hard to tell for me. Thanks for that too. I confirmed with the urls below. So I use Worksheet_Calculate and use something like if val(application.version)=10 then ... end if to provide activation for this or that version. http://msdn.microsoft.com/library/de...ableUpdate.asp http://msdn.microsoft.com/library/de...ableUpdate.asp http://support.microsoft.com/?kbid=291294 http://support.microsoft.com/default...b;EN-US;213220 http://support.microsoft.com/default...b;EN-US;161761 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Controlling Pivotitems.visible | Excel Discussion (Misc queries) | |||
Change Multiple Page Fields in two independent Pivot Tables | Excel Worksheet Functions | |||
Pivot Change PivotItems - Visible/Hide with VBA | Excel Worksheet Functions | |||
Filtering or linking Page Fields in an Excel Pivot Table | Excel Programming | |||
Add PivotItems to PivotTable / Enabling - Disabling PivotItems | Excel Programming |