Eigene Icons/Symbole zum Menüband hinzufügen

Wie man das Menüband individuell gestalten und um eigene Register und Sektionen erweitert kann, wurde vor einiger Zeit im Beitrag "Makro als Add-In einbetten" illustriert. Für die gewünschten Funktionen gibt es diverse Standard-Symbole, die man verwenden kann. Dies ist eine Übersicht:

StandardIcons

Je nach Anwendungsfall lässt sich jedoch kein passendes Bild finden - oder man strebt grundsätzlich eine komplett eigene Lösung an. Hier wird dargestellt, wie ein selbst erstelltes Symbol eingefügt werden kann: Die Bilddatei sollte im Format .png und quadratisch sein. Da es kleine Icons sind, sollten die Masse nicht über 256x256 Pixel sein. Ich habe ein Bild in Photoshop mit den Massen 100x100 Pixel erstellt und lokal abgespeichert:

ArrowPlus10

Die Funktion, die über dieses Symbol abgerufen werden soll, ist eine Erhöhung des selektierten Wertes um 10%. Um dieses Beispiel zu illustrieren, habe ich eine simple Umsatzplanung für das nächste Jahr erstellt:

Umsatzplanung2020

Es soll über die angesprochene Funktion entweder die aktuelle Menge oder der jetzige Preis um 10% erhöht werden, da sich der Umsatz ohnehin aus Preis * Menge zusammensetzt. Um dies zu bewerkstelligen, legst Du folgendes Makro an:

Sub IncreaseBy10Percent()

Dim CurrentValue As Double
Dim NewValue As Double

CurrentValue = ActiveCell.Value
NewValue = CurrentValue * 1.1
ActiveCell = NewValue

End Sub

(Beachte: Es sind keinerlei Kontrollen und Sicherheiten eingebaut. Es wird nicht überprüft, welcher Wert in der selektierte Zelle steht oder ob gar ein Bereich ausgewählt wurde - das kann zu Fehlermeldungen führen!)

Office RibbonX Editor

Um das Custom-Icon einzufügen, verwenden wir die Software "Office RibbonX Editor" (analog "Custom UI Editor"). Es handelt sich dabei nicht um ein Microsoft Produkt. Fernando Andreu hat das Tool auf GitHub zur Verfügung gestellt (hier geht es zur Release-Seite).

Nach dem Öffnen der Datei ist das gewünschte Excel-File auszuwählen (File → Open). Daraufhin fügen wir - je nach Excel-Version - einen "Custom UI Part" ein (ich verwende "Office 2010+"). Dies erfolgt via Rechtsklick auf die Excel-Datei im Editor:

InsertCustomUiPart

Als nächstes ist die erstellte Grafik einzufügen. Gehe dazu auf "Insert Icons". Wie im Screenshot unten ersichtlich ist das Icon ebenfalls innerhalb des Editors auf der linken Seite (Navigation) aufgeführt. Der fertige Code, um das entsprechende Icon ("ArrowPlus10.png") in ein neues Menüband-Register "Eigene Makros" und der Sektion "Umsatzplanung" einzubetten, ist unten dargestellt. Am Ende dieses Beitrags ist zudem der Code als "Zitat" zu finden - für Copy+Paste-Zwecke. Nach einer Anpassung in dem Editor kannst Du jederzeit auf "Validate" klicken, um den Code zu testen. Speichere die Änderungen im Editor ab ("Save") und öffne das Excel-File erneut.

RibbonXCode

Die obigen Bezeichnungen wie "Menüband-Register" oder "Sektion" können individuell ausgestaltet werden. Auch die Anordnung/Reihenfolge des Menübands kann hier beeinflusst werden (in Zeile 4: "insertAfterMso="). Im fertigen Screenshot unten habe ich unser eigenes Register hinter "TabHome" eingefügt, damit es auf der Abbildung besser ersichtlich ist:

RibbonSolution

Ein Klick auf diesen individuell gestalteten "Button" erhöht daraufhin den selektierten Wert wie gewünscht um 10%.

Dies ist der Code für den "Custom UI Part":

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<tabs>
<tab id="customTab" label="Eigene Makros" insertAfterMso="TabHelp">

<group id="customGroup" label="Umsatzplanung">
<button id="customButton1" label="Erhöhe Wert" size="large" onAction="IncreaseBy10Percent" image="ArrowPlus10" />

</group>
</tab>
</tabs>
</ribbon>
</customUI>

Loop durch Tabellenblätter mit Sheets.Index (VBA)

Angeknüpft an den letzten Beitrag zu dynamischen Formeln über mehrere Tabellenblätter, behandelt dieser Beitrag die Thematik, wie mittels einer VBA-Schleife (Loop) durch mehrere Tabellenblätter navigiert werden kann, ohne dass der Code jeweils anzupassen ist. [Eine Einführung zum hier angewandten "For"-Loop - einer VBA-Schleife - findest Du in diesem Beitrag.] Ziel ist es also, weitere Tabellenblätter einfügen zu können, ohne auf diese spezifisch im Code verweisen zu müssen.

Unser Beispielunternehmen weist Zahlen für die jeweiligen Regionen und Unternehmenseinheiten aus. Unten ist ein Beispiel dargestellt, bei dem unterschiedliche Kantone ("Basel", "Bern" und "Zürich") vorhanden sind. Zudem werden die Daten auf Konzernebene aggregiert (Tabellenblatt "Total"):

SheetIndexSheetsA

Wir gehen davon aus, dass jede einzelne Region die gleiche Struktur aufweist und wir demnach denselben Makro-Code auf alle Tabellenblätter anwenden können - ein perfekter Anwendungsfall also für einen Loop! In einem ersten Schritt sollten die notwendigen Befehle für ein einzelnes Tabellenblatt aufgezeichnet, geschrieben und getestet werden. Auf jedem Blatt sollen beispielsweise Spalten ausgeblendet sowie Formatierungen vorgenommen werden. Es bieten sich nun unterschiedliche Schleifen an, die sich über alle gewünschten Tabellenblätter erstrecken. In unserem Fall soll der Loop jedoch dynamisch sein, damit wir jederzeit neue Regionen einfügen können. Verweisen wir im Code demnach explizit auf die aktuell erste Region "Basel", hat dies später allenfalls keine Gültigkeit mehr (Beispiel: Wenn wir "Aargau" einfügen, rückt dieser Kanton an erste Stelle). Im anfangs verlinkten Beitrag zu dynamischen Formeln über mehrere Tabellenblätter haben wir Hilfstabellenblätter eingefügt ("Kanton_AA" und "Kanton_ZZ") und diese am Ende ausgeblendet:

SheetIndexSheetsZ

Durch diesen Mechanismus kann nun ein "For"-Loop angewandt werden. Unser Makro ermittelt den "Sheet.Index" dieser Hilfsblätter; das heisst, an welcher Stelle in der Datei diese Tabellenblätter stehen. Wir definieren hier einerseits die Variablen des Typs "Integer" (vereinfacht: Zahlen) im Makro:

Dim SheetIndexStart As Integer
Dim SheetIndexEnd As Integer

Danach kann der Indexwert dieser beiden Tabellenblätter ermittelt werden:

SheetIndexStart = Sheets("Kanton_AA").Index
SheetIndexEnd = Sheets("Kanton_ZZ").Index

In unserem Beispiel wird für "Kanton_AA" der Wert "2" als Variable abgespeichert, für "Kanton_ZZ" ist dies der Wert "6". Nun kann der "For"-Loop eingesetzt werden, der den Code mit den Befehlen für die Tabellenblätter umfasst:

For x = SheetIndexStart + 1 To SheetIndexEnd - 1
Sheets(x).Activate
'Befehle zur Bearbeitung der Tabellenblätter
Next

Da wir die Hilfsblätter nicht zwingend bearbeiten müssen, kannst Du entweder direkt bei der Definition der Variablen "+1" addieren respektive "-1" subtrahieren, oder wie im Code oben beim Beginn des "For"-Loop die Index-Zahl um eins erhöhen und reduzieren. Mit "Sheets(x).Activate" wird dann in jedem Durchlauf der Schleife jeweils das entsprechende Tabellenblatt aktiviert und bearbeitet. Beachte, dass Du die Hilfstabellenblätter "Kanton_AA" und "Kanton_ZZ" allenfalls zuerst durch das Makro einblenden musst. Füge hier zu Beginn Deines Makros einfach ein:

Sheets("Kanton_AA").Visible = True
Sheets("Kanton_ZZ").Visible = True

... und am Ende dasselbe, mit dem Boolean-Wert "False", damit die Blätter direkt wieder ausgeblendet sind.

Fügst Du nun eine neue Region (z.B. "Genf" oder "Aargau") ein, wird das Makro dank der Ermittlung des "Index-Wertes" auch diese Tabellenblätter in der Loop-Bearbeitung berücksichtigen können. Entsprechend reagiert die Selektion, falls Du weitere Tabellenblätter weiter vorne in der Datei einfügst (beispielsweise eine weitere Auswertung neben dem "Total") oder die Kantone mitsamt den beiden Hilfsblätter an eine andere Stelle innerhalb der Datei verschiebst.

Mehrere Tabellenblätter auf einmal ein- und ausblenden

In Excel können Tabellenblätter ein- und ausgeblendet werden. Dies bezweckt, dass dem Benutzer der Datei nur die relevanten Inhalte angezeigt werden. Unten aufgeführt ist ein Beispiel eines Berichtes mit drei verschiedenen Tabellenblättern: Dem "Bericht" selbst sowie die diesem zugrundeliegenden "Daten/Data" und ein Steuerungs-Blatt mit dem Namen "Parameter".

TabellenblaetterAlle

Die Empfänger dieses Berichtes interessieren lediglich die aufbereiteten Informationen. Die unübersichtlichen Quelldaten oder allfällige Steuerelemente im Hintergrund müssen nur in Einzelfällen oder bei Rückfragen aufgezeigt und offengelegt werden. Deshalb sollte der- oder diejenige, die den Bericht pflegt, diese Blätter vor dem Versand ausblenden. Dieser Schritt kann mittels Rechtsklick auf ein beliebiges Tabellenblatt → "Ausblenden" vollzogen werden:

TabellenblattAusblenden

Um mehrere "Sheets" auszublenden, sind die gewünschten Tabellenblätter im Vornherein zu selektieren (mittels CTRL + anklicken - oder im Falle von einer Vielzahl: Das erste Blatt anklicken, Shift drücken und das letzte Blatt ebenfalls anklicken; damit werden alle dazwischenliegenden Sheets ebenfalls markiert). Daraufhin erfolgt ebenfalls ein Rechtsklick auf ein beliebiges Tabellenblatt → "Ausblenden".

Um ein Tabellenblatt wieder einzublenden, geht man via Rechtsklick auf ein Blatt → "Einblenden". Es erscheint dann eine Liste mit sämtlichen ausgeblendeten Tabellenblätter:

TabellenblattEinblenden

Es ist das jeweilige Blatt auszuwählen und mit "OK" wird es alsbald eingeblendet. In der obigen Liste können jedoch nicht mehrere Blätter gleichzeitig selektiert und angezeigt werden. Das kann bei grossen Dateien zeitaufwändig und nervig sein. Um jedoch direkt alle ausgeblendeten Sheets anzuzeigen, ist ein Makro notwendig: Gehe via ALT+F11 oder mittels Rechtsklick auf ein Tabellenblatt → "Code anzeigen" in die VBA-Umgebung und füge ein neues Modul ein (Rechtsklick auf der linken Seite bei den Projekten → "Einfügen" →  "Modul". Füge dort dann den nachfolgenden Code ein:

Sub Einblenden()

Dim Tabellenblatt As Object

For Each Tabellenblatt In Worksheets
Tabellenblatt.Visible = True
Next Tabellenblatt

End Sub

Sobald dieses Makro ausgeführt wird, werden alle ausgeblendeten Tabellenblätter direkt wieder eingeblendet.