3

We have a little piece of VBA code that worked perfectly for ages. It was essentially:

Me.PivotTables("APivot").PivotFields("AField").CurrentPage = "Some text"

This worked until Excel 2013, where the line would fail with an unspecific error:

Runtime error 5:
Invalid procedure call or argument.

By trial and error we figured that in Excel 2013 you cannot navigate to a pivot table page with code until the user has navigated to that page manually using the Excel interface. As soon as the user navigates to a page, navigating to that page with code will always succeed (until you close the workbook). So in order for the code to be able to navigate to any page, we first need the user to go through all of them manually.

A workaround to that would be changing the value of the underlying cell instead:

Me.PivotTables("APivot").PivotFields("AField").CurrentPage.LabelRange.Value = "Some text"

We are currently forced to use this workaround, but it feels hackish.

Exactly what in Excel 2013 causes this behaviour? Is there some bit one now needs to do first in order to navigate to a page (poke something, load some data in some way etc)?

EDIT: Same problem occurs in Office 2016.

Community
  • 1
  • 1
GSerg
  • 71,102
  • 17
  • 141
  • 299
  • When you say 'manually', have you tried running code to move to that sheet and activate a cell? – Neil Mussett Mar 03 '13 at 02:57
  • @NeilMussett The sheet is active when the code executes. Moreover, if the Excel 2013 is in "compatibility mode" (that is, if the file has the xls extension), then everything works as usual. But if the file has been resaved as the new xlsm, it doesn't. – GSerg Mar 03 '13 at 05:24
  • Has this been resolved, I'm facing the same issue – Jakob May 26 '14 at 12:55
  • @Jakob I haven't resolved this. We decided to not roll out Excel 2013 after all, there were various issues. – GSerg May 26 '14 at 14:37
  • @Jakob Well, it has been resolved after all ;) – GSerg Apr 25 '17 at 16:35

1 Answers1

2

I'm not sure how they become "hidden" in the first place, but I just ran into the same problem and found this post.

The solution from there is to right-click the filter field (on the spreadsheet cell, not the field list) and under field settings, make sure the items are not hidden.

Tim Malone
  • 2,894
  • 4
  • 31
  • 44
Larryjl
  • 88
  • 6
  • 1
    This pointed me in the right direction. Thanks. Just as a point of clarification... I had to select "Select Multiple Value Items" from the filter, and then "Select All" from the items list, then uncheck "Select Multiple Value Items." It wasn't allowing me to unhide items without doing this first. – Searle Nov 14 '16 at 22:48