Force PageField filter to FALSE

S

steveski

I have a PivotTable with a PageField called “End Flag” which can b
either TRUE or FALSE. I use the drop-down to filter on FALSE. Thi
PivotTable is based on a data entry table where there will usually b
entries with “End Flag” = FALSE. However, it is possible that all “En
Flag” values could be TRUE. In this case the PageField filter revert
to TRUE, and must be manually reset to FALSE. This is a problem for m
because I intend to hide the PivotTable and turn the spreadsheet ove
to a user. If all the “End Flag” entries become TRUE, then th
PageField filter will revert to TRUE, then when new “End Flag” entrie
occur which are FALSE, they will not show up in the chart which i
based on this PivotTable.
Is there a way to force the PageField filter to only show FALSE, eve
when no “End Flag” entries are FALSE?
Thanks
 
B

Bernie Deitrick

Steveski,

Try recording a macro while setting your value to FALSE, then fire that
macro using the worksheet's activate or calculate event.

HTH,
Bernie
MS Excel MVP
 
S

steveski

I wound up using the following code:

Private Sub Workbook_Open()
On Error Resume Next
ActiveSheet.PivotTables("PivotTable1"). _
PivotFields("flag1").CurrentPage = "FALSE"
End Sub

This worked nicely. Is there a way I can display a message to the user
if they click on the chart when no “End Flag” entries are FALSE? (I
assume this would be keyed to the "On Error" statement?)
 
B

Bernie Deitrick

Steveski,

You could use some statement like this in the Pivot Chart's activate event:

Private Sub Chart_Activate()
If
Worksheets("Sheetname").PivotTables("PivotTable1").PivotFields("flag1").Pivo
tItems.Count = 0 Then
Msgbox "No items to show"
End If
End Sub

But you'll need to determine which pivotfield and actual property to use to
determine when state that you are interested in is actually occurring.

HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top