Gruppierungen per VBA ein- und ausblenden

Ein Bericht hat Detailzeilen, Zwischensummen und eine Gesamtsumme, gruppiert über Daten > Gliederung > Gruppieren. Mit den Ziffern 1, 2, 3 oben links klappt man die Ebenen auf und zu. Für die Geschäftsleitung soll ein Knopf „Nur Summen“ genau das per Klick erledigen.

Die Kurzlösung

ThisWorkbook.Worksheets("Bericht").Outline.ShowLevels RowLevels:=1      ' nur oberste Ebene
ThisWorkbook.Worksheets("Bericht").Outline.ShowLevels RowLevels:=8      ' alles anzeigen
ThisWorkbook.Worksheets("Bericht").Outline.ShowLevels ColumnLevels:=1   ' dasselbe für Spalten

RowLevels entspricht den Ziffern oben links. Excel kennt höchstens acht Ebenen, mit 8 ist also immer alles sichtbar, auch wenn der Bericht weniger Ebenen hat.

Ein kleines Modul für Knöpfe

Option Explicit

' Zeigt im Blatt "Bericht" die Zeilen bis zur gewünschten Gliederungsebene an.
' Ebene 1 zeigt nur die oberste Ebene, eine hohe Zahl (etwa 8) zeigt alles.
Public Sub GliederungZeigen(ByVal ebene As Long)
    ThisWorkbook.Worksheets("Bericht").Outline.ShowLevels RowLevels:=ebene
End Sub

Public Sub AllesZeigen()
    GliederungZeigen 8                              ' Excel kennt höchstens 8 Ebenen
End Sub

Public Sub NurSummenZeigen()
    GliederungZeigen 1
End Sub

' Legt Gruppen per Code an, etwa für einen Bericht, der jedes Mal neu entsteht.
Public Sub GruppenAnlegen()
    With ThisWorkbook.Worksheets("Bericht")
        .Outline.SummaryRow = xlSummaryBelow        ' Summenzeile unter der Gruppe
        .Rows("3:5").Group                          ' Ebene 2
        .Rows("7:9").Group
        .Rows("3:10").Group                         ' Ebene 1 um beide Blöcke
    End With
End Sub

' Gliederung auf einem geschützten Blatt weiter benutzbar machen (muss nach jedem Öffnen laufen).
Public Sub SchutzMitGliederung()
    With ThisWorkbook.Worksheets("Bericht")
        .EnableOutlining = True
        .Protect Password:="", UserInterfaceOnly:=True
    End With
End Sub

Die Makros NurSummenZeigen und AllesZeigen lassen sich direkt Schaltflächen zuweisen.

Was die Ebenen zeigen

Beispiel mit zwei inneren Gruppen (Zeilen 3 bis 5 und 7 bis 9) und einer äußeren Gruppe (Zeilen 3 bis 10):

Aufrufsichtbare Zeilen
GliederungZeigen 11, 2, 11, 12
GliederungZeigen 21, 2, 6, 10, 11, 12
AllesZeigen1 bis 12

Die Zeilen 6 und 10 sind die Summenzeilen der inneren Gruppen, Zeile 11 die der äußeren. Deshalb bleiben sie sichtbar.

Einzelne Gruppe auf- und zuklappen

Soll nur eine bestimmte Gruppe aufgehen, sprechen Sie ihre Summenzeile an. Liegt die Summe unter der Gruppe, ist das die Zeile direkt darunter, im Beispiel Zeile 11 für die äußere Gruppe 3 bis 10 und Zeile 6 für die Gruppe 3 bis 5:

ThisWorkbook.Worksheets("Bericht").Rows(11).ShowDetail = True    ' äußere Gruppe aufklappen
ThisWorkbook.Worksheets("Bericht").Rows(6).ShowDetail = False    ' innere Gruppe 3 bis 5 zuklappen

Eine innere Gruppe bleibt unsichtbar, solange die äußere zugeklappt ist. Klappen Sie deshalb zuerst die äußere Gruppe auf.

Gliederung auf geschützten Blättern

Ist das Blatt geschützt, lassen sich die Plus- und Minuszeichen normalerweise nicht mehr bedienen. EnableOutlining = True zusammen mit UserInterfaceOnly:=True gibt sie wieder frei. Diese Einstellung speichert Excel nicht in der Datei. Rufen Sie das Makro deshalb beim Öffnen auf, etwa in Workbook_Open.

Typische Fehler

  • Nichts passiert: Es gibt keine Gruppierung. ShowLevels wirkt nur auf vorhandene Gruppen. Mit GruppenAnlegen lassen sich Gruppen auch per Code erzeugen.
  • Summenzeile oben statt unten: Excel erwartet die Summenzeile standardmäßig unter der Gruppe. Liegen Ihre Summen darüber, setzen Sie Outline.SummaryRow = xlSummaryAbove, sonst verhalten sich die Ebenen unerwartet.
  • Blatt fest im Code ohne Prüfung: Wird das Blatt umbenannt, bricht das Makro mit Laufzeitfehler 9 ab. Nutzen Sie den Codenamen des Blatts, der sich beim Umbenennen nicht ändert.

Passende Beiträge

Lieber machen lassen?

Wir programmieren die Lösung für Ihre Datei.

Vom einzelnen Makro bis zur Anwendung für die ganze Abteilung. Mit KI-Unterstützung setzen wir vieles schneller und günstiger um, jede Lösung testen wir vor der Übergabe.