Excel 2011 spreadsheet does not change the current page until manually

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

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

      

This worked until Excel 2013, where the line ended up with a non-specific error:

Runtime error 5:
Invalid call or procedure argument.

Through trial and error, we realized that in Excel 2013, you cannot navigate to a PivotTable page with code until the user navigates to that page manually using the Excel interface. Once the user navigates to the page, navigating to that page with the code will always succeed (until you close the workbook). Therefore, in order for the code to be able to navigate any page, we first need the user to view all of them manually.

A workaround to this would change the base cell value:

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

      

We are currently forced to use this workaround, but it seems hacky.

What's causing this behavior in Excel 2013? Is there some bit that needs to be done first to get to the page (nudge something, load some data, etc.)?

EDIT: Same problem occurs in Office 2016.

+3


source to share


1 answer


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



The solution from here is to right click on the filter field (in the table cell, not the field list) and in the field settings, make sure the items are not hidden.

+2


source







All Articles