D
DataFreakFromUtah
No question here, just some procedures for the archive.
Search criteria: fill listbox with items from a pivottable field, get
pivot table field values into a listbox, populate listbox with items
from pivottable, listbox values from pivottable, get listbox values
from pivot table.
The three procedures below demonstrate how to fill a listbox with
items from a pivot table on the active worksheet, modify the pivot
table with a selection in
the listbox and then "reset" the modified field in the pivot table
with all values being shown. The target field in the pivottable is a
field call DEPT that will populate a listbox named ListBox1 on the
active sheet.
1. The first procedure, SetupListBox1, populates ListBox1
2. The second procedure, ListBoxSelectionChangesPT, modifies
(shows/hides) DEPT items in the pivottable based on the selection
highlighted in ListBox1 (note with this code, it seems that you can
only select one item at a time in the listbox, I tried adjusting
ListBox1> Properties>Behavior> MultiSelect: 0 - frmMultiSelectSingle,
but this modification conflicted with the code in the procedure. You
may have better luck or insight.
3. The third procedure, PivotShowItemAllVisible, shows all the
previously hidden items in the first field of the pivot table (in this
case, DEPT).
Please note that I gathered these procedures from previous postings in
the newsgroup and modified the existing code just slightly for clarity
and run-time accuracy.
Sub SetupListBox1()
'Clears then populates a listbox named LISTBOX1 on active sheet
'With identified values from .PivotFields("TargetFieldNameHere")
Dim PF As PivotField
Dim I As Integer
Set PF = ActiveSheet.PivotTables(1).PivotFields("DEPT")
With ActiveSheet.ListBox1
.Clear
For I = 1 To PF.PivotItems.Count
.AddItem PF.PivotItems(I)
Next
End With
End Sub
Sub ListBoxSelectionChangesPT()
'Note for this procedure to work it seems that your
'ListBox1 Properties>Behavior>MultiSelect must be set
'to 0 - frmMultiSelectSingle. So it seems you can only
'select one item in the listbox to update to the pivot table.
Dim PF As PivotField
Dim I As Integer
Dim iVis As Integer
Set PF = ActiveSheet.PivotTables(1).PivotFields("DEPT")
With ActiveSheet.ListBox1
For I = 1 To PF.PivotItems.Count
If .Selected(I) Then
PF.PivotItems(I).Visible = True
iVis = iVis + 1
End If
Next
If iVis = 0 Then
MsgBox "Must have at least one DEPT visible"
Exit Sub
End If
For I = 1 To PF.PivotItems.Count
If Not .Selected(I) Then PF.PivotItems(I).Visible = False
Next
End With
End Sub
Sub PivotShowItemAllVisible()
'Shows all items in the FIRST FIELD in all pivot tables
'on the active sheet.
'For version 2000 -- show all items in field
'sort is set to Manual to prevent errors, e.g.
'unable to set Visible Property of PivotItem class
Dim pt As PivotTable
Dim PF As PivotField
Dim pi As PivotItem
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each PF In pt.VisibleFields
For Each pi In PF.PivotItems
If pi.Visible <> True Then
pi.Visible = True
End If
Next pi
Next PF
Next pt
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Search criteria: fill listbox with items from a pivottable field, get
pivot table field values into a listbox, populate listbox with items
from pivottable, listbox values from pivottable, get listbox values
from pivot table.
The three procedures below demonstrate how to fill a listbox with
items from a pivot table on the active worksheet, modify the pivot
table with a selection in
the listbox and then "reset" the modified field in the pivot table
with all values being shown. The target field in the pivottable is a
field call DEPT that will populate a listbox named ListBox1 on the
active sheet.
1. The first procedure, SetupListBox1, populates ListBox1
2. The second procedure, ListBoxSelectionChangesPT, modifies
(shows/hides) DEPT items in the pivottable based on the selection
highlighted in ListBox1 (note with this code, it seems that you can
only select one item at a time in the listbox, I tried adjusting
ListBox1> Properties>Behavior> MultiSelect: 0 - frmMultiSelectSingle,
but this modification conflicted with the code in the procedure. You
may have better luck or insight.
3. The third procedure, PivotShowItemAllVisible, shows all the
previously hidden items in the first field of the pivot table (in this
case, DEPT).
Please note that I gathered these procedures from previous postings in
the newsgroup and modified the existing code just slightly for clarity
and run-time accuracy.
Sub SetupListBox1()
'Clears then populates a listbox named LISTBOX1 on active sheet
'With identified values from .PivotFields("TargetFieldNameHere")
Dim PF As PivotField
Dim I As Integer
Set PF = ActiveSheet.PivotTables(1).PivotFields("DEPT")
With ActiveSheet.ListBox1
.Clear
For I = 1 To PF.PivotItems.Count
.AddItem PF.PivotItems(I)
Next
End With
End Sub
Sub ListBoxSelectionChangesPT()
'Note for this procedure to work it seems that your
'ListBox1 Properties>Behavior>MultiSelect must be set
'to 0 - frmMultiSelectSingle. So it seems you can only
'select one item in the listbox to update to the pivot table.
Dim PF As PivotField
Dim I As Integer
Dim iVis As Integer
Set PF = ActiveSheet.PivotTables(1).PivotFields("DEPT")
With ActiveSheet.ListBox1
For I = 1 To PF.PivotItems.Count
If .Selected(I) Then
PF.PivotItems(I).Visible = True
iVis = iVis + 1
End If
Next
If iVis = 0 Then
MsgBox "Must have at least one DEPT visible"
Exit Sub
End If
For I = 1 To PF.PivotItems.Count
If Not .Selected(I) Then PF.PivotItems(I).Visible = False
Next
End With
End Sub
Sub PivotShowItemAllVisible()
'Shows all items in the FIRST FIELD in all pivot tables
'on the active sheet.
'For version 2000 -- show all items in field
'sort is set to Manual to prevent errors, e.g.
'unable to set Visible Property of PivotItem class
Dim pt As PivotTable
Dim PF As PivotField
Dim pi As PivotItem
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each PF In pt.VisibleFields
For Each pi In PF.PivotItems
If pi.Visible <> True Then
pi.Visible = True
End If
Next pi
Next PF
Next pt
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub