Issues with setting .EnableMultiplePageItems

Dear Readers,

Problem: I want the page filter of a pivot table that has been passed one value for display to show that value in the page filter field (as opposed to showing "Multiple Values" even though only one item is selected). For example, if I have passed the value of "Baltimore" to the pivot field (which happens to be a page filter field), then I want "Baltimore" to show in the cell, not "Multiple Items". To force the issue, I am using .EnableMultiplePageItems = FALSE to deselect the "Select Multiple Items" checkbox on the page filter. The results are baffling: sometimes it works and sometimes it doesn't, sometimes that box is selected and it shows "Multiple Items" (even though only one is selected) and sometimes the box is selected but it resolves to the value of the selected item anyway (to show "Baltimore").

Question: How do I force the pivot table filter field to show the name of the [single] selection and not show "Multiple Items" when only one selection is made?

More detail:  I have a series of Excel workbooks used to generate reporting, where the data represented is managed by a "focus" pivot table. Users make selections in slicers connected to this "focus" pivot; calculations that drive charts and other representations are fueled by whether a record is currently displayed in the pivot or not. Any one of these workbooks may have been created in Excel 2010 or 2013, or even 2007. They are automated with VBA (mostly to generate slides from filtered views of the data). The automation uses a table to populate an array variable with keywords for actions as well as names of objects to act against.

The problem I am experiencing involves the use of the pivot's page filters. A single item passed to the filter selects the records that are displayed in the chart, and a field above the chart displays the current value of the page filter so that the user knows what [in this case] market he is seeing,  I need the user to see the market name (e.g. "Baltimore") and not "Multiple Items". To ensure this at run time, I employ the PivotField.EnableMultiplePageItems property set to "False" to deselect the "Select Multiple Items" checkbox on the page filter in question. Here is the line as it appears in my code:
     
wbSourceFile.Sheets(p(0, cint_y_ws, 0)).PivotTables(p(0, cint_y_pt, 0)).PivotFields(p(x, 0, cint_z_field)).EnableMultiplePageItems = False

Open in new window


The p() array variables resolve correctly to name the sheet, pivot table, and pivot field (which is a page filter field).  The problem is that it isn't actually affecting a change to the pivot table in runtime. Well, to be more accurate, sometimes it does and sometimes it doesn't. I am utterly baffled by the behavior I am observing. It does not appear to be restricted to one workbook, nor is the behavior consistent in multiple runs of the same workbook. Yesterday I ran all my daily reports successfully (market names appearing as they should) until my last workbook: to get it to run with the market names showing (instead of "Multiple Items" showing), I had to change the FALSE to TRUE for this statement. I did it on a whim never expecting it to actually work. This morning  ran selected reports both ways - once with EnableMultiplePageItems set to True and once to False: both runs worked on all reports to show the market name (!?) except one which would only work to show the market name if I set .EnableMultiplePageItems = TRUE. I have also run these tests on another machine (different hardware, different installation of Excel) and gotten the same inconsistent results.

If I record and run the action of setting the .EnableMultiplePageItems property in one of these workbooks it works fine:
Sub Macro1()
    Sheets("Focus").Select
    ActiveSheet.PivotTables("FocusPivot1").PivotFields("Area").ClearAllFilters
    ActiveSheet.PivotTables("FocusPivot1").PivotFields("Area").EnableMultiplePageItems = False
    ActiveSheet.PivotTables("FocusPivot1").PivotFields("Area").CurrentPage = "(All)"
End Sub
Sub Macro2()
    ActiveSheet.PivotTables("FocusPivot1").PivotFields("Region").EnableMultiplePageItems = True
End Sub
Sub testToggle()
    ActiveSheet.PivotTables("FocusPivot1").PivotFields("Region").EnableMultiplePageItems = _
        Not (ActiveSheet.PivotTables("FocusPivot1").PivotFields("Region").EnableMultiplePageItems)
End Sub

Open in new window


Doesn't look like rocket science does it, but running this statement,
wbSourceFile.Sheets(p(0, cint_y_ws, 0)).PivotTables(p(0, cint_y_pt, 0)).PivotFields(p(x, 0, cint_z_field)).EnableMultiplePageItems = False

Open in new window

which I can see is resolving correctly and not erroring out, works sometimes but not others, and sometimes only works with the property value reversed.  Stepping through the code in various scenarios with different workbooks on different machines has only further confused me, since I can't detect a pattern to the behavior.

Has anyone else seen similar behavior? Does anyone heard if this is some kind of bug in Excel 15.0.4745.0000 (Office Plus 2013 32-bit)? Am I just plain trying to solve the problem the wrong way?

I am about to whack the pivot cache on the most problematic workbook and rebuild it, but I really don't want to have to do that for all the workbooks. If that doesn't work, I may need to rebuild the workbook from the floor up and it's pretty complex, so I'd rather not do that if I don't have to. If anyone has any suggestions, ideas, previous experience, rank conjecture - I'm open to it all. Please and thank you...

Because I really don't think that it's little spreadsheet demons tormenting me with inconsistent behaviors, despite how it feels right now.
klundbergAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

klundbergAuthor Commented:
Dear Readers,

Sorry that it took me so long to get back to this. I have discovered the problem. I'm not sure if this is a deliberate design choice or a bug, but here goes:

The action of setting the .EnableMultiplePageItems to True marks the "Select Multiple Items" checkbox  as selected, but it also automatically selects all the available pivot items. That has the effect of nullifying your previous selection of a single pivot item. The action of setting this value manually does not result in the same follow-on behavior: the pivot items are only re-selected when performing this action via VBA.  Unless you know that it does that, the subsequent apparent behavior of that applying that action with VBA becomes confusing and inexplicable.

This additional behavior (that of marking all the pivot items in the page field as selected) is not documented anywhere that I could find. I had not noticed it the first couple of times that I walked through the code - I honestly didn't expect that behavior so I was looking at the wrong things.

To accommodate the fact that setting the .EnableMultiplePageItems to true also reset my pivot item selections, I simply moved the line before where I made my pivot item selection.

If anyone has any insight into whether this is intended behavior or a bug, I'd love to hear it. It's annoying that it performs an additional action that is not performed when checking that box manually. It's even more annoying that it doesn't appear to be documented in Microsoft's MSDN site.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.