VBA - Setting Pivot Item to Visible False Errors

[cfoye130]

I have a pivot table with a pivot field of Week Date.

My vba tries to hide all of the week dates except for the current week date. It gets that current week date value from a cell in the sheet. It works fine and hides every date that is not the current week date until it gets to 1 from the end, and then errors with a 1004, unable to set visible class. Any ideas? Code is listed below. Thanks!

For Each w In ThisWorkbook.Worksheets
  For Each pt In w.PivotTables
    pt.RefreshTable
    pt.Update
  Next
Next
 
For Each w In ThisWorkbook.Worksheets
  For Each pt In w.PivotTables
    Set pf = pt.PivotFields("Week Date")
    pf.ClearAllFilters
 
For Each pi In pf.PivotItems
  If pi = Worksheets("Control Sheet").Range("B10").Value Then
    'pi.Visible = True
  Else
    pi.Visible = False
  End If
Next pi
Next
Next

I've stepped through the code and when it gets to the date it will error on, it recognizes that it is in fact the Else, then goes to pi.visible = False, but then errors when it tries to set the property.

 

[DonkeyOte]

You would need one visible item at all times, no ?
(It seems your code is hiding everything)

In general - ensure a valid item is unhidden before then running a hide routine.

 

[cfoye130]

It is unhidden because I cleared the filter right? It shows on the table, and I can manually set it to visible through the ui. And I could post the rest of the code, but that may just confuse things as I know the rest of the code works fine.

 

[DonkeyOte]

With the below - do you get the MsgBox dialog prior to the Debug or not ?

For Each pi In pf.PivotItems
  If pi = Worksheets("Control Sheet").Range("B10").Value Then
    MsgBox "exists"
  Else
    pi.Visible = False
  End If
Next pi

It might be that the date comparison is going awry

Which version are you using ?

 

[cfoye130]

I get the debug prior to recieving the error message. But I would expect to since the comparision is going to go to the "Else", rather than the "Then".

I can't send you the entire workbook due to propriatery info but I can send you a sample that shows you what is happening....

 

[DonkeyOte]

OK, perhaps I didn't explain that very well...

The prior code was designed to flag if any item in the Pivot was "matching" your criteria value - any iterated Item(s) that matched would have generated a MsgBox dialog.

Given you had no Dialog it would seem that all of your items are being hidden.
When you try to hide the very last item you will get the Debug as it would mean no visible items.

Date comparisons can be tricky and there are a few oddities with Pivot Dates pending XL version - it would help to know which version you're using.

 

[cfoye130]

ahhh, sorry '07.

Can I send you a sample file to take a look at?

 

[cfoye130]

And when I F8 through the code, it goes one pivot item at a time, and sucesfully hides them until it gets to 12/06/2010, with 12/13/2010 remaining. So it is not like it is trying to hide all items in the table. And because it doesnt get to 12/13/2010 before it errors I would suspect thats why I dont see the message box.

 

[DonkeyOte]

OK... so I've now seen the file and can confirm the following / obscure bug

Environment:
XL2007 (holds true also in XL2010)
US Regional Setting
Short Date set to mm/dd/yyyy (important - if left/set as m/d/yyyy the below issues are not encountered)

Ambiguous dates (eg 8th Nov, 6th Dec) make it impossible to select the pivot item by it's .value or .caption and even using it's index position it is impossible to toggle .visible status

This odd date behaviour is not new per se and is something I've seen before in earlier versions Pivots but which could be handled by modifying the NumberFormat of the Field prior to Evaluation.
However, XL2007+ (excl. Compatibility Mode obviously) there is no NumberFormat option for non Data Field items (modifying the default format has no bearing on outcome).

I am wondering what can be done here... ?

For sake of replication... assume a table of info per below with Pivot configured as seen on System Settings outlined previously:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Project	Hours	Week Date
2	A	40	12/06/2010
3	B	6	12/13/2010		Sum of Hours	Column Labels
4					Row Labels	12/06/2010	12/13/2010	Grand Total
5					A	40		40
6					B		6	6
7					Grand Total	40	6	46
Sheet1

The below will simply return an error:

?Sheet1.PivotTables(1).PivotFields("Week Date").PivotItems(1).Visible

whereas

?Sheet1.PivotTables(1).PivotFields("Week Date").PivotItems(2).Visible

will not

I've not been able to find a way to reference Item 1 successfully using it's value/caption etc ... casting etc all fails.

 

[DonkeyOte]

OK, the above sample is misleading as what works for that does not work for the bigger file...

After much head scratching and with a not insignificant amount of help from starl the below process would appear to resolve the issue given the specifics of the data & configuration:

1. format source dates as General

2. format column header row of Pivot as General

3. refresh PT
(for sake of clarity it is probably worth setting PT Options -> Data -> Retained Items to None)

4. revert column header row format to Date (optional)

5. modify VBA to account for "(blank)" item and explicitly cast criteria to Date

Regards the specifics of point 5 ... in terms of the "test" code we had been using separately this would look like:

.Visible = Val(.Value) = CDate(Worksheets("Control Sheet").Range("B10").Value)

In terms of your original code approach this would look like:

For Each pi In pf.PivotItems
    pi.Visible = Val(pi.Value) = CDate(Worksheets("Control Sheet").Range("B10").Value)
Next pi

Downside here is that the source dates must remain in General format else the old issue will resurface.

I (really) hope this helps.