Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel

Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel
Full audit trails to underlying transactions from P&Ls, Balance Sheets, graphs, PivotTables, and departmental TBs in a stand-alone Excel file. Aged Debtors and Aged Creditor files too. Free 30 day trials. Download today at www.onionrs.co.uk

Saturday 6 December 2014

Run-time error '1004': Unable to set the Visible property of the PivotItem class

There are two circumstances in which this error will arise when issuing a [PivotItem].Visible = True command in Visual Basic:


  1. The sort order of the PivotField containing the PivotItem is set to anything other than xlManual; and,
  2. The ShowAllItems value of the PivotField containing the PivotItem is set to False and an attempt is made to set a PivotItem containing no data (i.e. [PivotItem].RecordCount = 0) to True.
To process [PivotItem].Visible = True safely you should issue the following commands first:

[PivotField].AutoSort xlManual, [PivotField].SourceName
[PivotField].ShowAllItems = True


No comments:

Post a Comment