J
JonS
Hello,
On a pivot table you can elect to hide selected values on any field.
You do this by double-clicking on the field name and highlighting the
relevant items in the "Hide Items" text box that appears.
My question is this: What is the VBA code to detect any of these
selections a User may have made?
Allow me to explain and I will attempt to try an clarify this somewhat
consusing (to me at least) area.
My research so far to discover this code has been on the
"PivotItems(i).Visible" property, but this seems to tell you what
items happen to be visible on the pivot table at the time. This would
change by simply changing the Page File fields or say, by removing a
field from the pivot table alltogether. This is different. This is not
actually the question I am asking.
Because a pivot table is dynamic in nature, I want to be able to
detect, for the purposes of data integrity, whether particular field
values have been suppressed, irrespective of whether that field
happpens to be visible on the table or not. I want to be able to state
to the User that they have elected to suppress certain field values,
whether they would ordinarily appear on the pivot table or not.
I also tried the HiddenFields and VisibleFields properties. But again,
these fields seem to relate to "what you actually see" on the pivot
table at the time. For example, if you were to remove the field off
the pivot, then this property lists all the field items as "hidden",
or perhaps all the fields that are not visible due to the various
filtering effects of the Page Files. (Actually, I could not seem to
easily see the difference between the results of these two
properties).
I hope I have not been two confusing, but to restate: a User can
suppress particular field values by the "double-click method"
described above. Of course, if the field value isn't there for the
particular pivot table view, then it doesn't make any difference.
But it IS important to know that a User has set that particular field
value to "hidden" so that the User can be warned that if the value
ordinarly should show, it won't.
It is a definitly a field click-text box setting a User can set. Just
double-click on a pivot field heading to find it. What is the VBA code
to grab this setting? Thanks,
JonS
On a pivot table you can elect to hide selected values on any field.
You do this by double-clicking on the field name and highlighting the
relevant items in the "Hide Items" text box that appears.
My question is this: What is the VBA code to detect any of these
selections a User may have made?
Allow me to explain and I will attempt to try an clarify this somewhat
consusing (to me at least) area.
My research so far to discover this code has been on the
"PivotItems(i).Visible" property, but this seems to tell you what
items happen to be visible on the pivot table at the time. This would
change by simply changing the Page File fields or say, by removing a
field from the pivot table alltogether. This is different. This is not
actually the question I am asking.
Because a pivot table is dynamic in nature, I want to be able to
detect, for the purposes of data integrity, whether particular field
values have been suppressed, irrespective of whether that field
happpens to be visible on the table or not. I want to be able to state
to the User that they have elected to suppress certain field values,
whether they would ordinarily appear on the pivot table or not.
I also tried the HiddenFields and VisibleFields properties. But again,
these fields seem to relate to "what you actually see" on the pivot
table at the time. For example, if you were to remove the field off
the pivot, then this property lists all the field items as "hidden",
or perhaps all the fields that are not visible due to the various
filtering effects of the Page Files. (Actually, I could not seem to
easily see the difference between the results of these two
properties).
I hope I have not been two confusing, but to restate: a User can
suppress particular field values by the "double-click method"
described above. Of course, if the field value isn't there for the
particular pivot table view, then it doesn't make any difference.
But it IS important to know that a User has set that particular field
value to "hidden" so that the User can be warned that if the value
ordinarly should show, it won't.
It is a definitly a field click-text box setting a User can set. Just
double-click on a pivot field heading to find it. What is the VBA code
to grab this setting? Thanks,
JonS