Excel Pivot Tables, Page Fields, Visible PivotItems, Activation

[Ananda Sim]

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

 

[Ananda Sim]

Hi Sebastien,

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/d.../en-us/vbaxl10/html/xlevtPivotTableUpdate.asp

http://msdn.microsoft.com/library/d.../en-us/vbaxl11/html/xlevtPivotTableUpdate.asp

http://support.microsoft.com/?kbid=291294

http://support.microsoft.com/default.aspx?scid=kb;EN-US;213220

http://support.microsoft.com/default.aspx?scid=kb;EN-US;161761