1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113
|
Public champ As String
Public feuille As Worksheet
Public tcd As PivotTable
Sub CreateDialog()
Dim dlg, d, sh As Shape, lstb As Object
Dim pi As PivotItem, dlgExist As Boolean
Application.ScreenUpdating = False
' A parametrer suivant tes donnees
Set feuille = Feuil1 ' feuille du tcd
champ = "MES" ' champ de page
dlgExist = False
For Each d In ThisWorkbook.DialogSheets
If d.Name = "dlgTCD" Then
dlgExist = True
Exit For
End If
Next d
If Not dlgExist Then
Set dlg = ThisWorkbook.DialogSheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
dlg.Name = "dlgTCD"
Set sh = dlg.Shapes(1)
sh.TextFrame.Characters.Text = "Select filtering values for [" & champ & "]"
Set lstb = dlg.ListBoxes.Add(sh.Left + 10, sh.Top + 20, sh.Width - 100, sh.Height - 30)
lstb.Name = "ListBoxTCD"
lstb.MultiSelect = xlSimple
Else
Set dlg = ThisWorkbook.DialogSheets("dlgTCD")
Set lstb = dlg.ListBoxes("ListBoxTCD")
lstb.RemoveAllItems
End If
Set tcd = feuille.PivotTables(1)
For Each pi In tcd.PivotFields(champ).PivotItems
lstb.AddItem pi.Value
Next pi
i = 1
For Each pi In tcd.PivotFields(champ).PivotItems
If pi.Visible = True Then
lstb.Selected(i) = True
Else: lstb.Selected(i) = False
End If
i = i + 1
Next pi
dlg.Shapes(2).OnAction = "FilterPivot"
With dlg.Shapes(3)
.TextFrame.Characters.Text = "Select All"
.OnAction = "SelectAllList"
End With
dlg.Visible = True
dlg.Activate
dlg.Shapes(3).Select
With Selection
.DefaultButton = False
.CancelButton = False
.DismissButton = False
.HelpButton = False
.Accelerator = ""
End With
dlg.Visible = False
feuille.Activate
Application.ScreenUpdating = True
dlg.Show
Set d = Nothing
Set pi = Nothing
Set lstb = Nothing
Set sh = Nothing
Set dlg = Nothing
End Sub
Sub FilterPivot()
Dim dlg, lstb As Object
Set dlg = ThisWorkbook.DialogSheets("dlgTCD")
Set lstb = dlg.ListBoxes("ListBoxTCD")
nb = 0
For i = 1 To lstb.ListCount
If i < lstb.ListCount Or (i = lstb.ListCount And nb > 0) Then
If lstb.Selected(i) Then
tcd.PivotFields(champ).PivotItems(lstb.List(i)).Visible = True
nb = nb + 1
Else: tcd.PivotFields(champ).PivotItems(lstb.List(i)).Visible = False
End If
Else
If lstb.Selected(i) Then
tcd.PivotFields(champ).PivotItems(lstb.List(i)).Visible = True
Else ' No item selected
For j = 1 To lstb.ListCount
tcd.PivotFields(champ).PivotItems(lstb.List(j)).Visible = True
Next j
End If
End If
Next i
tcd.PivotFields(champ).CurrentPage = "(Tous)"
End Sub
Sub SelectAllList()
Dim dlg, lstb As Object
Set dlg = ThisWorkbook.DialogSheets("dlgTCD")
Set lstb = dlg.ListBoxes("ListBoxTCD")
If dlg.Shapes(3).TextFrame.Characters.Text = "Select All" Then
For i = 1 To lstb.ListCount
lstb.Selected(i) = True
Next i
dlg.Shapes(3).TextFrame.Characters.Text = "Un-select All"
Else
For i = 1 To lstb.ListCount
lstb.Selected(i) = False
Next i
dlg.Shapes(3).TextFrame.Characters.Text = "Select All"
End If
End Sub |
Partager