XL 2019 Sous Total

Sub AfficherToutesLesLignes()
' Afficher toutes les lignes
Dim LigneTS As Long
Dim MaSomme
    Application.ScreenUpdating = False
    On Error Resume Next
    For LigneTS = 1 To Range("Tableau1").ListObject.ListRows.Count
        MaSomme = WorksheetFunction.Sum(Range("Tableau1").ListObject.ListRows(LigneTS).Range)
        Sheets(Range("Tableau1").Parent.Name).Range("Tableau1[#Headers]").Resize(1, 1).Offset(LigneTS, -1).Value = MaSomme
    Next LigneTS
    Range("Tableau1").Rows.Hidden = False
End Sub

Sub Afficher()
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
With Range("Tableau1")
        .EntireColumn.Hidden = False
        .EntireRow.Hidden = False
End With
End Sub

Sub Afficher()
With Range("Tableau1")
        .AutoFilter
        .EntireColumn.Hidden = False
        .EntireRow.Hidden = False
        .AutoFilter
End With
End Sub

Sub Afficher()
With [Tableau1]
    .AutoFilter: .AutoFilter 'ôte le filtrage automatique
    .Rows.Hidden = False
    .Columns.Hidden = False
End With
End Sub

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

Private Sub Workbook_Deactivate()
          Application.CommandBars("Column").Reset
End Sub

Private Sub Workbook_Open()
     If ActiveSheet Is Sh_Test Then ChangeMenuContextuelColonnes
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
     If Sh Is Sh_Test Then
          ChangeMenuContextuelColonnes
     Else
          Application.CommandBars("Column").Reset
     End If
End Sub