Scroll Bars und Spin Buttons (Formularsteuerelemente)

Möchtest Du Deine Formulare und Reports um weitere Funktionalitäten aufwerten? Hier werden die zwei Schaltflächen "Scroll Bars" und "Spin Buttons" vorgestellt (Deutsch: "Scrollleisten" und "Drehfelder"). Wir verwenden hier die regulären "Formularsteuerelemente" und es werden zwei konkrete Anwendungsfälle illustriert. Es gäbe zudem die Möglichkeit "ActiveX-Steuerelemente" einzupflegen. Diese bieten noch mehr Optionen in der individuellen Gestaltung (z.B. visuelles Design oder Verhalten). Aufgrund der Komplexität beschränken wir uns in dieser Einführung jedoch auf Formularsteuerelemente und verwenden keinerlei Makros. Um ein solches Element einzufügen, gehe unter dem Menüband auf "Entwicklertools" → "Einfügen" und wähle das entsprechende Objekt aus. Das Drehfeld und die Scrollleiste findest Du hier:

ScrollBarMenu   SpinButtonMenu

Es ist durchaus möglich, dass bei Dir der Menüpunkt "Entwicklertools" noch nicht vorhanden ist. Gehe dazu in die Excel-Optionen (oder via Rechtsklick auf das Menüband → "Menüband anpassen") und selektiere bei den "Hauptregisterkarten" die Entwicklertools:

Unten werden Dir nun zwei Beispiele und mögliche Anwendungen dieser Formularsteuerelemente gezeigt.

Scrollleiste / Scroll Bar

In einem ersten Beispiel zeige ich Dir eine Scrollleiste, mit der wir mittels eines Reglers einen Prozentwert justieren können. Dadurch lassen sich beispielsweise direkt und interaktiv Auswirkungen auf Prämien und Boni veranschaulichen. Unten aufgeführt ist ein Umsatzziel von CHF 1'000'000, das es zu erreichen gibt. Mit einer Scroll Bar kann nun der tatsächlich generierte Umsatz justiert und ein "Szenario" simuliert werden; der Gewinn wird direkt unterhalb angezeigt (setze dazu einfache Formeln wie "=C8-C7"):

ScrollBarData  ScrollBarData2

Mit dem Regler in Zeile 4 kann der Prozentwert eingestellt werden. Beachte: Die Zelle "D3" wird als Hilfsfeld verwendet - hier wird der "Output" der Scroll Bar dargestellt. In Feld "B3" wird dann dieser Wert als Prozent dargestellt (=D3/100). Die Optionen und Einstellungen dieser Scrollleiste lauten wie folgt:

ScrollBarProperty

Mit "Seitenwechsel" wird konfiguriert, wie stark der Marker springt, sobald man einen der beiden Pfeile betätigt. Die "Schrittweite" betrifft die Einstellung und das Intervall, sobald man neben den Regler klickt (auf die Fläche daneben, nicht auf die Pfeile selbst).

Um Prozentwerte mit Dezimalstellen zu erlangen, nimm beispielsweise diese Optionen vor:

ScrollBarProperty2

Du siehst: Der Maximalwert ist nun auf 1'000 eingestellt. Die Formel mit Bezug auf die Hilfszelle wird ebenfalls durch 1'000 geteilt. Das Endresultat:

ScrollBarPercent

Drehfeld / Spin Button

Um ein Drehfeld zu illustrieren, gehen wir von einem Kuchenrezept aus. Ein Feld - eben ein "Drehfeld" - dient dazu, die Anzahl Personen und somit die Mengenangaben für den Kuchen zu verändern. Wir pflegen wie im Beispiel oben Hilfszellen ein (Spalte F in der Abbildung unten), wo die Mengenangaben für 1 Person angegeben werden. Innerhalb des Rezepts kann darauf referenziert werden (für Zeile 8: "=F8*$B$5").

SpinButtonData

Die Optionen dazu lauten wie folgt:

SpinButtonProperty

Die Darstellung der Zutaten als Bruch erlangst Du in der regulären Zellformatierung (wähle "Bruch" anstatt z.B. "Zahl" oder "Währung").

(Dieses Rezept stammt von Fooby)

Weitere Formatierungen

Deine Scroll Bar kann horizontal (wie oben) oder aber auch vertikal ausgerichtet sein. Achte beim Einfügen darauf, wie Du das Objekt und dessen Grösse bestimmst. Hier ein Beispiel, wie Du vertikale oder horizontale Scrollleisten einfügen kannst:

ScrollBarsOrientation

Alternativ kannst Du im Nachgang zudem über die Optionen des Steuerelements die Grösse anpassen:

ScrollBarSize

(Beachte: Du musst die Höhe oder Breite allenfalls zweimal eingeben, da die Proportionen des Objekts möglicherweise beibehalten werden.)

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.

(Dynamische) Formeln über mehrere Tabellenblätter

Bei dynamischen Dateien ist es wichtig, dass Formeln möglichst auf Veränderungen reagieren und diese ohne weiteren Aufwand durch den Benutzer stets die korrekten Daten anzeigen. Ein Unternehmen weist betriebsintern beispielsweise Zahlen für die jeweiligen Regionen und Unternehmenseinheiten aus. Unten ist ein Beispiel dargestellt, bei dem unterschiedliche Regionen ("Basel", "Bern" und "Zürich") vorhanden sind. Zudem werden die Daten auf Konzernebene aggregiert (Tabellenblatt "Total").

SheetIndexSheetsA

Die einzelnen Regionen weisen jeweils die gleiche Struktur auf, wobei auf dem Total-Tabellenblatt einfach auf die einzelnen Zellen Bezug genommen werden kann. Die Formel "=SUMME(Basel:Zürich!C8)" summiert die Zelle "C8" von sämtlichen Tabellenblättern (von "Basel" bis "Zürich"). Sollte nun eine weitere Region (beispielsweise "Genf") hinzugefügt werden, wird der Wert in Zelle "C8" zusätzlich berücksichtigt - sofern das Blatt "Genf" zwischen Basel und Zürich eingeordnet wird. Falls jedoch "Aargau" dazukommen sollte und an der alphabetischen Reihenfolge festgehalten wird, müsste die SUMME-Formel entsprechend angepasst werden. Eine weitere Möglichkeit ist jedoch die nachfolgende: Um die SUMME-Formel keineswegs anpassen zu müssen, fügen wir links und rechts der Regionen weitere leere Tabellenblätter ein ("Kanton_AA" und "Kanton_ZZ") und blenden diese aus. Unsere SUMME-Formel sollte sich nun über diese Hilfsblätter erstrecken:

=SUMME(Kanton_AA:Kanton_ZZ!C8)

SheetIndexSheetsZ

Da die Hilfsblätter leer sind, wird zwar "C8" ebenfalls aggregiert, aber dies entspricht jeweils dem Wert 0 und hat deshalb keinen Einfluss auf das Gesamttotal. Mit dieser Herangehensweise werden die unterschiedlichen Szenarien bereits von Anfang an aufgefangen und die Daten in "Total" basieren jederzeit auf allen Regionen.