Gruppierung reduzieren / erweitern auf gesperrtem Tabellenblatt

Gruppierungen werden in Excel-Berichten eingesetzt, um bestimmte Inhalte transparent anzeigen oder ausblenden zu lassen. Es zeigt deutet dem Leser an, dass noch weitere Informationen in der Tabelle vorhanden sind, die möglicherweise eingesehen werden sollten. Im Unterschied dazu gibt es die Möglichkeit, Zeilen oder Spalten komplett auszublenden. Dort erfordert es vom Leser mehr Aufmerksamkeit, um zu bemerken, dass Inhalte verborgen sind. Man bemerkt möglicherweise einen sprunghaften Anstieg in den Zeilennummerierungen oder den Spalten. Für welche Variante man sich entscheidet, ist natürlich situativ.

Worum es allerdings in diesem Blog im Spezifischen geht: Hast Du einmal versucht, Gruppierungen in einem geschützten und gesperrten Tabellenblatt auf- oder einzuklappen? Das ist nicht möglich und die Rechte können nicht entsprechend vergeben werden. Es sei denn, man wendet zwei Zeilen VBA-Code an. Es wird die nachfolgende Fehlermeldung angezeigt:

ErrorGroupings

Hierfür gibt es zwei Möglichkeiten, die ich nachfolgend vorstelle: Eine Schaltfläche, mit welcher der Benutzer die +/- Symbole "aktiviert" oder dass das Makro direkt beim Öffnen der Datei ausgeführt wird. Für beide Lösungsansätze musst Du zunächst in die Makro-Umgebung wechseln (ALT + F11 oder mittels Rechtsklick auf ein Tabellenblatt → "Code anzeigen"). Auf der linken Seite findest Du Deine Tabelle wieder. Mit einem Rechtsklick auf kannst Du via Einfügen → Modul alles soweit vorbereiten, um Dein Makro einzurichten. Füge dann folgenden Code ein:

Sub GruppierungenErlauben()

Sheets("MeinTabellenblatt").Protect Password:="MeinPasswort", UserInterfaceOnly:=True
Sheets("MeinTabellenblatt").EnableOutlining = True

End Sub

Ersetze bitte "MeinTabellenblatt" mit dem Namen Deines Excel-Sheets und vergebe ein Passwort, mit dem der Schutz aufzuheben ist. Mit "UserInterfaceOnly" wird lediglich die Benutzereingabe eingeschränkt; ein Makro könnte nach wie vor Änderungen in den gesperrten Bereichen vornehmen. Durch die weitere Angabe "EnableOutlining" wird die Berechtigung erteilt, auf die Gruppierungen zuzugreifen - trotz Blattschutz. Um dem Benutzer im Tabellenblatt die Möglichkeit zu geben, die Funktion zu aktivieren, füge beispielsweise ein Textfeld ein. Mit einem Rechtsklick auf dieses Objekt findest Du die Option "Makro zuweisen", woraufhin Du das Makro "GruppierungenErlauben" dem "Button" zuweisen kannst.

Makro beim Öffnen der Datei ausführen

Meiner Meinung nach schöner gelöst ist es, wenn Du die Codezeilen direkt ausführst, wenn die Datei geöffnet wird. Um dies zu bewerkstelligen, füge nicht ein neues Modul ein, sondern navigiere in der VBA-Umgebung auf "DieseArbeitsmappe" (ThisWorkbook in Englisch). Füge dort den nachfolgenden Code ein (achte bitte auf die angepasste 1. Code-Zeile):

Private Sub Workbook_Open()

Sheets("MeinTabellenblatt").Protect Password:="MeinPasswort", UserInterfaceOnly:=True
Sheets("MeinTabellenblatt").EnableOutlining = True

End Sub

So wird der Code entsprechend ausgeführt, wenn das Excel-File geöffnet wird.

Bitte beachte, dass Du die Excel-Datei als Makrodatei (.xlsm) oder Binärdatei (.xlsb) abspeichern musst, damit die Makros enthalten bleiben.

Collections in VBA

Wer Daten von einer Quelle in eine Zieltabelle überträgt, kann mit den .Copy und .Paste-Methoden ein gewünschtes Resultat erzielen. Ist man jedoch häufig dazu gezwungen, zwischen Tabellenblättern oder Excel-Dateien hin und her zu wechseln, hat das Implikationen auf die Laufzeit des Makros. Eine performante Variante, um mehrere Datensätze zu übertragen, bietet der Einsatz von Collections. Beim einleitend beschriebenen Fall werden zuerst alle Datensätze der Collection hinzugefügt und daraufhin auf der Zieltabelle eingetragen. In diesem Blog wird dieses Beispiel illustriert und abschliessend eine weitere Möglichkeit aufgezeigt, wie Collections zusätzlich genutzt werden können.

Die nachfolgenden Tagesumsätze sollen auf eine zweite Tabelle übertragen werden:

CollectionUmsaetze

Natürlich könnte man alle Daten selektieren und mittels .Copy und .Paste fortschreiben, wir verwenden aber zur Übung und Illustration eine Collection dazu. Wir definieren demnach die Variable "UmsatzColl". Zudem ist die Collection zu initiieren. Verwende hierzu diese beiden Codezeilen:

Dim UmsatzColl As Collection
Set UmsatzColl = New Collection

Um beispielsweise den Wert in Zelle "C4" Deiner Collection hinzuzufügen, verwende:

UmsatzColl.Add Range("C4").Value

Man führt hinter der Collection-Variable also die Methode "Add" hinzu, gefolgt vom Bezug auf den Wert, den man zwischenspeichern möchte. Zusätzlich zum Wert könnte ein "Key" angegeben werden mit dem jederzeit auf den effektiven Wert referenziert werden kann. Hier könnte beispielsweise das Datum des Umsatzes hinzugefügt werden. Desweiteren kann man den Wert sortieren oder einordnen lassen, indem man die fakultativen "Before" oder "After"-Angaben mitgibt.

CollectionProperties

Neben "Add" stehen weitere Methoden zur Verfügung, sie werden Dir angezeigt, sobald Du "UmsatzColl." schreibst:

CollectionMethods

Man fügt Werte zur Sammlung hinzu, kann mit "Count" ausgeben, wie viele Werte in der Collection sind und mit "Remove" spezifische Einträge entfernen. Mit "Item" kann ein Wert ausgegeben werden (es ist jedoch die Standardeigenschaft und muss nicht zwingend angegeben werden) - ich werde das weiter unten aufführen.

Wir haben oben einen spezifischen Wert (Zelle "C4") hinzugefügt. Mit einer For-Schleife werden wir alle Umsätze durchgehen (Zeile 2 bis 24), die Integer-Variable "i" unterstützt diesen Loop:

For i = 2 To 24
UmsatzColl.Add Range("C" & i).Value
Next

 Um nun die Daten auf einer anderen Tabelle abzurufen, verwende ich wiederum einen For-Loop. Dabei wird wieder "i" verwendet, beginnend bei 1 bis hin zur Anzahl Items, die in der Collection sind (UmsatzColl.Count). Die Werte werden in eine Spalte A geschrieben:

For i = 1 To UmsatzColl.Count
Range("A" & i) = UmsatzColl(i)
Next

Alternativ könntest Du auch UmsatzColl.Item(i) verwenden, wie oben erwähnt - das Resultat ist dasselbe.

In der Praxis verwende ich teilweise auch mehrere Collections in Kombination. Eine "StartCopyRowColl" und "EndCopyRowColl" beispielsweise, wenn mehrere auseinanderliegende und unterschiedlich grosse Bereiche zu kopieren sind. Zusammen gibt es spannende und sehr performante Lösungen, da der Index ("i") in beiden Collections korreliert.

Ein weiterer Tipp: Du musst nicht alle Items mit "Remove" entfernen, wenn Du die Collection in der gleichen Prozedur erneut verwenden möchtest. Führe einfach die Codezeile "Set UmsatzColl = New Collection" erneut auf, dann beginnst Du mit Deiner Sammlung wieder von vorne.

Abhängige (dynamische) Drop-Down-Listen

In Formularen sind abhängige Drop-Down-Listen oftmals unabdingbar. Was bedeutet das? Wählt man im ersten Feld eine Oberkategorie "Obst" oder "Fleischwaren", dann sollen in einer zweiten Auswahlliste nur die entsprechenden Einträge zur Option stehen. Wir haben diese abhängigen Drop-Down-Listen bereits im Beitrag "Dropdown einer Dropdown-Liste erstellen - abhängige Dropdown" illustriert. Das funktioniert dann einwandfrei, wenn sich die Inhalte der Unterkategorien nicht ändern. Ansonsten ist es notwendig, die Bereiche (Namen) von "Obst" oder "Fleischwaren" den neuen Umständen entsprechend anzupassen (Bereich erweitern oder reduzieren). Es suggeriert also eine dynamische Drop-Down-Liste. Wie man einen solchen Namen (mit der Formel BEREICH.VERSCHIEBEN) anlegt, kannst Du in diesem Beitrag nachschlagen: Bereich eines Namens automatisch erweitern

Wir würden also alle Namen für die Drop-Down-Listen entsprechend anlegen, wie im Screenshot unten dargestellt (mit der Formel "BEREICH.VERSCHIEBEN", referenzierend auf das separate Blatt "Parameter"):

Namenslisten-Dynamisch

Wählt man im Anschluss im Formular die erste Option zu "Essen", sollte in der abhängigen Drop-Down-Liste dann die jeweilige Unterkategorie abrufbar sein. Dies ist jedoch nicht der Fall, die Auswahl-Liste lässt sich mit Klick auf den Erweiterungspfeil gar nicht erst aufrufen und anzeigen. Wie können wir also sowohl abhängige wie auch dynamische Drop-Down-Listen erstellen? Das Vorgehen zeige ich Dir nachfolgend Schritt für Schritt auf, lösche jedoch bitte im Namensmanager (Menüpunkt "Formeln" → "Namensmanager") allfällige bereits definierten Namen für Deine Drop-Down-Listen.

Abhängige, dynamische Drop-Down-Listen (Lösungsansatz)

Wir müssen die ursprünglichen Daten auf dem separaten Blatt "Parameter" also anders definieren, um jederzeit unsere Auswahl-Kriterien ergänzen und unmittelbar nachher im Formular abrufen können. Gehe dazu im Menüband auf das Register "Einfügen" und selektiere die Option "Tabelle". Du erstellst nun also eine Tabelle und wählst als erstes den Bereich "A1:A3" aus, um die Oberkategorie abzubilden. Falls Du die Zeile 1 inkludierst, wähle zudem "Tabelle hat Überschriften".

TabelleEinfuegenUndDefinieren

Wiederhole den Vorgang auch für die Unterkategorien "Fleischwaren" und "Obst".

Wenn Du nun in der Spalte A Fleischwaren und Obst markierst, siehst Du links oben im Namensfeld (normalerweise steht da die Zelle "A2" und so weiter) "Tabelle1" stehen. Der Name "Tabelle1" ist aktuell also stellvertretend für diesen Bereich. Klicke in dieses Namensfeld und überschreibe einfach "Tabelle1" mit "Essen" - bestätige mit der Enter-Taste. Du wirst in dem Namensfeld kurz etwas aufblinken sehen, danach steht allerdings wieder "Tabelle1" drin. Mache das nun auch für B2:B3 (=Tabelle2) und setze da den Namen "Fleischwaren" sowie für C2:C4 (=Tabelle3) und definiere da "Obst". Rufe nun erneut Deinen Namensmanager auf (Menüpunkt "Formeln" → "Namensmanager"). Dort siehst Du die Resultate entsprechend - beachte auch die Spalte "Bezieht sich auf", die auf die jeweilige Tabelle referenziert:

NamensmanagerTabellenReferenzen

Navigiere nun auf Dein effektives Formular. Wähle die Hauptkategorie (Essen) und versuche nun, Deine Unterkategorie (Drop-Down-Einstellung: =INDIREKT(B5) -- wobei B5 dem Feld mit Fleischwaren/Obst entspricht) kann nun korrekt aufgerufen werden:

FormularDynamischeDropDownUnterkategorie

Zuletzt kannst Du prüfen, ob Deine Listen auf dem Blatt "Parameter" tatsächlich dynamisch sind. Gib einfach einen neuen Begriff unterhalb ein, beispielsweise "Schweinefleisch" als zusätzliche Fleischware. Die Tabelle erweitert sich automatisch (Du siehst dies auch farblich) - und auf der Drop-Down-Liste im Formular kannst Du die neue Option ebenfalls gleich selektieren.