Show or hide item (or items) in PivotTable for a certain field.
Passing PivotTable name in PivotName, then name of field to be filtered
Then Pass 1 in Show1_or_Hide2 to show these items, or pass 2 in it to hide them
Then finally, the actual item to be showen/hidden, or list of items separated by Sepa (default to |)
Will return 1 if applied with no errors, returns 0 if there was an error.
Function PivotTable_ShowHideItems(PivotName, PivotField, PivotItems, Show1_Or_Hide2, Optional Sepa = "|", Optional WB = "This", Optional Shee="Active")
' Show or hide item (or items) in PivotTable for a certain field.
' Passing PivotTable name in PivotName, then name of field to be filtered
' Then Pass 1 in Show1_or_Hide2 to show these items, or pass 2 in it to hide them
' Then finally, the actual item to be showen/hidden, or list of items separated by Sepa (default to |)
' Will return 1 if applied with no errors, returns 0 if there was an error.
If WB = "This" Then WB = ThisWorkbook.Name
If WB = "Active" Then WB = ActiveWorkbook.Name
If Shee = "Active" Then Shee = ActiveSheet.Name
Rett = 0
ShowTrue = True
If Show1_Or_Hide2 = 2 Then ShowTrue = False
On Error Resume Next
With Workbooks(WB).Worksheets(Shee).PivotTables(PivotName).PivotFields(PivotField)
For Each Itt in Split(PivotItems , Sepa)
If ItT > "" Then
.PivotItems(ItT).Visible = ShowTrue
End If
Next
End With
If Err.Number = 0 Then Rett = 1
On Error Goto 0
Err.Clear
PivotTable_ShowHideItems = Rett
End Function
' Show or hide item (or items) in PivotTable for a certain field.
' Passing PivotTable name in PivotName, then name of field to be filtered
' Then Pass 1 in Show1_or_Hide2 to show these items, or pass 2 in it to hide them
' Then finally, the actual item to be showen/hidden, or list of items separated by Sepa (default to |)
' Will return 1 if applied with no errors, returns 0 if there was an error.
If WB = "This" Then WB = ThisWorkbook.Name
If WB = "Active" Then WB = ActiveWorkbook.Name
If Shee = "Active" Then Shee = ActiveSheet.Name
Rett = 0
ShowTrue = True
If Show1_Or_Hide2 = 2 Then ShowTrue = False
On Error Resume Next
With Workbooks(WB).Worksheets(Shee).PivotTables(PivotName).PivotFields(PivotField)
For Each Itt in Split(PivotItems , Sepa)
If ItT > "" Then
.PivotItems(ItT).Visible = ShowTrue
End If
Next
End With
If Err.Number = 0 Then Rett = 1
On Error Goto 0
Err.Clear
PivotTable_ShowHideItems = Rett
End Function
PivotName, PivotField, PivotItems, Show1_Or_Hide2, Optional Sepa = "|", Optional WB = "This", Optional Shee="Active"
Views 158
Downloads 56
CodeID
DB ID
