Error "Unable to set the Visible property of the PivotItem class"

Dear All,

Here's another headscratcher for all of you experts!

I have searched the Internet for a solution to this problem but, although many people appear to have encountered problems similar to mine, I have been unable to find an approach which works.

My worksheet contains just a single (but large!) pivot table, and I wish to manipulate various pivot items in various pivot fields.

The following code does not do anything particularly useful (it's used just for debugging!), but the point is that it works perfectly for pivot fields (e.g. "Task Name") which do not contain dates, whereas problems arise when processing a pivot item in a date-related pivot field (e.g. "Billing Doc Date").

Please Login or Register  to view this content.

It is probably worth noting that the "faulty" (i.e. date-related) pivot item DOES exist, as it is possible to create an object reference to it, and many of the properties of this object are correctly exposed, but with the noticeable exception of the "Visible" property!!! - i.e. the first MsgBox statement displays the information required, whereas the second MsgBox statement produces the "Type Mismatch" error message.

Also, no AutoSort has been applied to the above pivot fields. The "Task Name" pivot field contains 970 pivot items, and the "Billing Doc Date" pivot field contains 400 pivot items.


In summary:

(a) in the case of the "Task Name" pivot field, all of the above code works correctly;

(b) in the case of the "Billing Doc Date" pivot field:

(1) the first MsgBox statement works correctly;

(2) the line "MsgBox pvtItem.Visible" produces the "Type Mismatch" error message;

(3) commenting-out the above line allows the line "pvtItem.Visible = False" to be executed and thereby produce the "Unable to set the Visible property of the PivotItem class" error message.

Any information or suggestions regarding the above would be much appreciated.

Best regards,

Greg M

A sample file would help here.

I know from prior threads that Pivot Dates are handled differently between versions in terms of assumed formatting on part of VBA, however, without data / source info. (external etc...) / version info. it's hard to offer anything useful.

Try adding:

Please Login or Register  to view this content.

prior to first Set line (it may seem odd but it's a probable fix see this old thread for a similar issue)