Sub DéfMasque()
Dim C As Range
txt = ""
For Each C In [TS_test[#Headers]].Offset(0, 1).Resize(1, [TS_test[#Headers]].Columns.Count - 1)
txt = txt & "," & Abs(Not C.EntireColumn.Hidden)
Next
txt = "={" & Mid(txt, 2) & "}"
ThisWorkbook.Names.Add "Masque", txt
End Sub
Sub ChangeMenuContextuelColonnes()
Dim menuCol As CommandBar
Dim Ctrl As CommandBarControl
Set menuCol = Application.CommandBars("Column")
menuCol.Reset
On Error Resume Next
menuCol.Controls("Masquer").Delete
menuCol.Controls("Afficher").Delete
On Error GoTo 0
With menuCol.Controls.Add(Type:=msoControlButton, Temporary:=True)
.Caption = "&Masquer (personnalisé)"
.OnAction = "AfficherMasquer"
.Parameter = "Masquer"
.BeginGroup = True
End With
With menuCol.Controls.Add(Type:=msoControlButton, Temporary:=True)
.Caption = "&Afficher (personnalisé)"
.OnAction = "AfficherMasquer"
.Parameter = "Afficher"
End With
End Sub
Sub AfficherMasquer()
Paramètre = CommandBars.ActionControl.Parameter
Selection.EntireColumn.Hidden = Paramètre = "Masquer"
If Intersect(Selection.EntireColumn, Sh_Test.[TS_Test]) Is Nothing Then Exit Sub
DéfMasque
With Sh_Test.[TS_Test].ListObject
AvecMasqués = False
For Each C In Sh_Test.[TS_Test].EntireColumn
If C.Hidden Then AvecMasqués = True: Exit For
Next
If AvecMasqués Then
.Range.AutoFilter Field:=.ListColumns("Somme").Index, Criteria1:="<>0"
Else
If .AutoFilter.FilterMode Then
.AutoFilter.ShowAllData
End If
End If
End With
End Sub