Software für Sie.

Im folgenden Beispiel erscheint beim SchlieÃ?en der Arbeitsmappe (egal, wie) eine Meldung mit Ja- und Nein-Button.

Wird der Button Ja betätigt, wird die Mappe gespeichert und geschlossen.

Beim Button Nein wird der Vorgang mit Cancel = True abgebrochen, die Mappe wird auch nicht geschlossen.

In das Modul DieseArbeitsmappe:

Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim strMsg As String strMsg = "Soll die Arbeitsmappe geschlossen werden?" Select Case MsgBox(strMsg, vbInformation + vbYesNo) Case vbYes: ThisWorkbook.Save Case vbNo: Cancel = True End Select End Sub

Hier ist eine kleine Anleitung zum Probieren.
Nimm Dir am besten eine neue Mappe und vollziehe die folgenden Schritte nach.

• Wechsle mit Alt + F11 in den VBA-Editor und klicke dort im Projektexplorer (normalerweise oben links) in Deine Mappe.
• Wähle jetzt Einfügen - Userform. Zeichne in diese Userform oben eine Textbox (Textfeld). Die Eingaben in dieses Textfeld sollen dann sofort in die Zelle A2 der aktiven Tabelle übernommen werden.
• Klicke doppelt auf die Textbox. Daraufhin erscheint das Klassenmodul der Userform, in das die Prozeduren eingetragen werden, die bei der Arbeit mit den Userform-Elementen ausgeführt werden sollen.
• Jetzt siehst Du auch schon zwei Zeilen, die Du so vervollständigst (Eingaben in die Textbox werden damit sofort in A2 der aktiven Tabelle übernommen):

Private Sub TextBox1_Change() range("A2") = TextBox1.Text End Sub

• Wechsle nun über das Menü Fenster oder mit der Tastenkombination Strg + F6 wieder zur Userform. Zeichne dort eine weitere Textbox und eine Schaltfläche (Ã?bernehmen) unter dieser Textbox.
• Doppelklicke jetzt auf die Schaltfläche und Du landest wieder im Klassenmodul.
• Es sind zwei neue Zeilen hinzugekommen, die Du so ergänzt:

Private Sub CommandButton1_Click() Range("A3") = TextBox2.Text End Sub

Der zweiten Textbox ist diesmal keine Prozedur zugeordnet, also passiert beim Eintragen erstmal nichts. Der Inhalt dieser Textbox wird erst beim Klick auf die Schaltfläche in A3 übernommen.

• Eigentlich ist die Userform jetzt schon zur Arbeit bereit. Was wäre aber ein Dialogfeld ohne SchlieÃ?en-Schaltfläche? Also basteln wir schnell noch eine. Deshalb - wieder zurück zur Userform (Fenster oder Strg + F6).
• Male nun unten in die Userform eine Schaltfläche und doppelklicke darauf.
• Die Prozedur müÃ?te diesmal so vervollständigt werden, sie ist dafür zuständig, daÃ? die Userform geschlossen wird:

Private Sub CommandButton2_Click() Unload Me End Sub

• Die Beschriftungen der Schaltflächen kannst Du noch ändern, indem Du wieder zur Userform zurückkehrst und zweimal auf die Schaltfläche klickst (kein Doppelklick!).
• So, jetzt müssen wir noch für den Aufruf sorgen, denn ein Anwender soll ja nicht jedesmal in den Editor wechseln, um die Userform aufzurufen. Die Prozedur dafür schreibt man normalerweise in ein allgemeines Modul - also wähle Einfügen - Modul.
• In das leere Modul schreibst Du:

Sub Aufruf() UserForm1.Show End Sub

• Wechsle nun wieder zu Excel und zeichne aus der Formular-Symbolleiste eine Schaltfläche auf das Blatt (moderner wäre eine Schaltfläche aus der Steuerelemente-Toolbox, aber wir wollen es ja am Anfang nicht so schwer machen). Jetzt öffnet sich ein Dialogfeld, in dem Du der Schaltfläche ein Makro zuweisen kannst - also Doppelklick auf Aufruf - denn so hatten wir das Makro ja benannt.
• Das wars. Jetzt kannst Du auf die Schaltfläche klicken, die Userform erscheint und Du wirst sehen, daÃ? Eintragungen in die erste Textbox sofort übernommen werden, Eintragungen in die zweite Textbox erst beim Klick auf die Schaltfläche.

Zusammenfassung:

In Deiner Mappe hast Du jetzt:

• ein Modul, auf dem die Prozedur zum Aufruf der Userform enthalten ist,
• eine Userform mit zwei Textboxen und zwei Schaltflächen (Commandbuttons),
• das Klassenmodul der Userform, auf dem die Routinen enthalten sind, die bei der Arbeit mit den Userform-Elementen aufgerufen werden.

Und nochmal der Tip: Im Editor kann man ganz einfach mit Strg + F6 zwischen den einzelnen Komponenten wechseln. Mit Strg + Tab ist es zwar auch möglich, bereitet aber Probleme, wenn Multipage-Elemente enthalten sind.

Der Code zum Kopieren:

Private Sub TextBox1_Change() Range("A2") = TextBox1.Text End Sub Private Sub CommandButton1_Click() Range("A3") = TextBox2.Text End Sub Private Sub CommandButton2_Click() Unload Me End Sub Sub Aufruf() UserForm1.Show End Sub

Bei der Arbeit mit Userforms gibt es immer verschiedene Wege, die zu einem Ziel führen. Die Kunst ist immer, Techniken/Konzeptionen zu wählen, die den eigenen Anforderungen am besten entsprechen; die am sichersten sind, möglichst kurze Laufzeiten aufweisen und Ressourcen am besten schonen. Darum soll es hier aber nicht gehen. An dieser Stelle stehen grundsätzliche Möglichkeiten im Mittelpunkt, die aufzeigen, wie die Aufgabenstellung prinzipiell gelöst werden kann. Auf Fehlerbehandlungen, Ergonomie und Schönheit wird hier deshalb verzichtet.

Für das Beispiel wird die abgebildete kleine Tabelle auf dem Blatt Daten verwendet; funktionieren soll dann die Userform1 mit den folgenden grundsätzlichen Elementen:

• Listbox1 (das wird die Obstliste)
• TextBox1
• TextBox2
• TextBox3
• TextBox4
• CommandButton1 (Neu)
• CommandButton2 (SchlieÃ?en)

Tipp: Auch wenn wir hier die Standardbezeichnungen der Elemente verwenden - übersichtlicher ist es, wenn Sie jedem Element eine aussagekräftige Eigenschaft bei Name vergeben. Commandbutton1 könnte so zum Beispiel cmdNeu genannt werden.

Nun müssen wir uns schon entscheiden: Der Code kann in ein allgemeines Modul (Modul1, Modul2, �) oder in diesem Fall in das Klassenmodul der Userform. Nehmen wir ein allgemeines Modul, muss im Code immer die gesamte Userform angesprochen werden, denn allgemeine Module stehen für die gesamte (in diesem Fall) Mappe - wenn mit mehreren Userforms gearbeitet würde, müsste der Code dort wissen, welche Userform angesprochen werden soll. Wir entscheiden uns hier für das Klassenmodul der Userform, das konkret dieser Userform zugeordnet ist.

Klassenmodul der Userform: Basis für den Code

In das Modul der Userform gelangt man am einfachsten und schnellsten, indem man auf das Element, für das der Code erstellt werden soll, doppelklickt. Wir wollen zuerst einmal dafür sorgen, dass die Userform per Klick auf Schlie�en geschlossen werden kann. Dazu also ein Doppelklick auf den Button (Commandbutton2 war das). Wir gelangen in das Klassenmodul, haben schon den vorbereiteten Code, der für das Klicken auf den Button zuständig ist und tragen dort einfach Unload Me ein:

Private Sub CommandButton2_Click() Unload Me End Sub

Mit F5 kann das auch gleich getestet werden Userform aufrufen und Button anklicken.

Einlesen der Daten aus der Tabelle

Der nächste Schritt ist das Einlesen der Daten von der Tabelle in die Listbox. Dies soll geschehen, wenn die Userform aufgerufen, also geladen wird.

Dazu wählen wir - wenn es noch nicht eingestellt ist - links oben das Element, die Userform. Rechts wählen wir das Ereignis, also wann es geschehen soll. Das ist hier Initlialize.

Wenn wir das gewählt haben, steht auch schon der Code dafür da, den wir vervollständigen. Im ersten Beispiel wählen wir die gebundene Form - die Daten erscheinen in der Listbox genau so, wie sie in der Tabelle stehen:

Private Sub UserForm_Initialize() ListBox1.RowSource = "Daten!B2:B10" End Sub

Manchmal kann es jedoch notwendig sein, die Daten ungebunden in die Liste einzutragen, wenn zum Beispiel Einträge ergänzt werden sollen. Im folgenden Beispiel wird dies gezeigt, indem die Daten aus Spalte A vorangestellt werden. Verwendet werden hier die Daten von Zeile 2 bis zur letzten in Spalte 2 ausgefüllten Zelle:

Private Sub UserForm_Initialize() 'ListBox1.RowSource = "Daten!B2:B10" Dim lngZ As LongPtr With Sheets("Daten") For lngZ = 2 To .Cells(Rows.Count, 2).End(xlUp).Row ListBox1.AddItem .Cells(lngZ, 1) & " - " & .Cells(lngZ, 2) Next End With End Sub

Im zweiten Beispiel sind die Daten ungebunden in der Liste - ändern sich die Daten in der Tabelle, bleibt die Listbox so, wie sie erstellt wurde.

Details zu gewähltem Listeneintrag anzeigen

Nun können wir unsere Einträge in der Liste anklicken, aber es passiert natürlich noch nichts. Dass rechts in den Textboxen die Details zum angeklickten Eintrag erscheinen, müssen wir erst eingeben. Es soll etwas beim Anklicken der Listbox passieren - also doppelklicken wir im Editor doppelt auf die Listbox und haben den Rahmen für den Code:

Private Sub ListBox1_Click() End Sub

Zunächst einmal ist wichtig, aus welcher Zeile des Tabellenblattes die Daten kommen sollen, wenn wir auf einen Eintrag in der Listbox klicken.

Dazu nutzen wir die Listindex-Eigenschaft der Listbox. Wenn wir in einer Listbox einen Eintrag auswählen, hat der immer einen Listindex, beginnend mit 0. Ist kein Eintrag ausgewählt, ist der Listindex -1.

Wenn wir also den ersten Eintrag anklicken, sollen die Daten aus der Zeile 2 erscheinen, beim zweiten Eintrag die Daten aus Zeile 3 usw. Wir addieren also zum Listindex einfach 2 und verwenden für die einzelne Textbox den jeweiligen Eintrag aus den Spalten 1 bis 4. Das kann so aussehen:

Dim intIndex As Integer intIndex = ListBox1.ListIndex With Sheets("Daten") TextBox1.Text = .Cells(intIndex + 2, 1) TextBox2.Text = .Cells(intIndex + 2, 2) TextBox3.Text = .Cells(intIndex + 2, 3) TextBox4.Text = .Cells(intIndex + 2, 4) End With

Nun kann allerdings der Fall eintreten, dass mal kein Eintrag gewählt ist. Dann sollen die Textboxen leer sein. Dafür verwenden wir hier eine kleine Schleife - da wir das später nochmal benötigen, lagern wir das in eine separate Sub() aus, die wir dann einfach aufrufen:

Sub TextBoxenLeer() Dim ctrControl As Control For Each ctrControl In Me.Controls If ctrControl.Name Like "TextBox*" Then ctrControl.Text = "" Next End Sub

Damit vervollständigen wir den Code der Listbox, so dass er so aussieht und wir es mit z. B. F5 testen können:

Private Sub ListBox1_Click() Dim intIndex As Integer intIndex = ListBox1.ListIndex If intIndex >= 0 Then With Sheets("Daten") TextBox1.Text = .Cells(intIndex + 2, 1) TextBox2.Text = .Cells(intIndex + 2, 2) TextBox3.Text = .Cells(intIndex + 2, 3) TextBox4.Text = .Cells(intIndex + 2, 4) End With Else TextBoxenLeer End If End Sub

�nderungen aus der Userform in die Tabelle übernehmen

Nun sollen �nderungen, die in den Textboxen vorgenommen werden, auch in die Tabelle übernommen werden. Hierbei ist es besonders wichtig, wie man vorgeht - bei vier Textboxen ist das sicher kein Problem, aber bei grö�eren Datenmasken kann bei einer unzweckmä�igen Technik schon viel überflüssiger Code entstehen. Für den Anfang sei hier jedoch nur auf das Erstellen einer separaten Klasse verwiesen oder auch auf das Verwenden eines Frames und das komplette �bernehmen mit einem Klick. Der Einfachheit halber weisen wir das �bernehmen hier den Textboxen direkt zu.

Damit wir nicht bei jeder Textbox überflüssigen Code wiederholen müssen, bereiten wir eine Sub() vor, die wir dann bei den Textboxen nur noch aufrufen. Die Sub() macht nichts anderes, als einen Wert in die Zelle lngZeile, lngSpalte zu schreiben:

Sub DatenInTabelle(ByVal lngZeile As LongPtr, ByVal lngSpalte As LongPtr, varWert) If lngZeile >= 2 Then Sheets("Daten").Cells(lngZeile, lngSpalte) = varWert End Sub

Damit das bei �nderungen in den Textboxen geschieht, müssen wir dazu noch den Code erstellen - auch hier wieder durch Doppelklick auf die einzelnen Textboxen. Dadurch erhalten wir die Code-Rahmen für die Boxen und fügen nur noch den Verweis auf die Sub ein, die wir gerade erstellt haben. Das sieht dann so aus:

Private Sub TextBox1_Change() DatenInTabelle ListBox1.ListIndex + 2, 1, TextBox1.Text End Sub Private Sub TextBox2_Change() DatenInTabelle ListBox1.ListIndex + 2, 2, TextBox2.Text End Sub Private Sub TextBox3_Change() DatenInTabelle ListBox1.ListIndex + 2, 3, TextBox3.Text End Sub Private Sub TextBox4_Change() DatenInTabelle ListBox1.ListIndex + 2, 4, TextBox4.Text End Sub

Wir sehen, dass auch hier für die Angabe der Zeile der Listindex der Listbox verwendet wird. Da der beim ersten Eintrag 0 ist, die Tabelle aber bei Zeile 2 beginnt, müssen wir hier noch die 2 addieren. Für den Fall, dass mal kein Eintrag in der Listbox gewählt ist (entspricht Listindex = -1), haben wir in Sub DatenInTabelle() noch die Abfrage If lngZeile >= 2 Then .

Damit sollte eine �nderung bei einem gewählten Eintrag übernommen werden. Da es hier um das Prinzip geht, lassen wir hier das Aktualisieren der Listeneinträge bei �nderungen der Spalten 1 und 2 au�en vor. Erwähnt sei nur, dass dies einfach mit ListBox1.List(ListBox1.ListIndex) = möglich ist.

Neuer Eintrag

Fehlt eigentlich nur noch eins: Wie fügt man einen neuen Eintrag hinzu?

In diesem Fall machen wir es ganz einfach: Wir lassen in die erste leere Zelle in Spalte 2 (B) und in die Listbox einen Eintrag vornehmen und diesen in der Listbox auswählen. Der Rest funktioniert dann bereits.

Private Sub CommandButton1_Click() Dim lngZ As LongPtr With Sheets("Daten") lngZ = .Cells(Rows.Count, 2).End(xlUp).Row + 1 .Cells(lngZ, 2) = "Neuer Eintrag" End With ListBox1.AddItem "Neuer Eintrag" ListBox1.ListIndex = ListBox1.ListCount - 1 End Sub

Der Nachteil ist, dass die Liste dann diesen Eintrag behält, aber das lässt sich leicht beheben. Am einfachsten schreibt man den Code aus Einlesen der Daten aus der Tabelle in eine separate Sub und lässt die beim Aufruf der Userform und beim Erstellen eines neuen Eintrags ausführen.

Viel Erfolg!

Eigentlich gar nicht.

Man kann aber mit einer Formel Bezug auf eine geschlossene Mappe nehmen; deren Ergebnis erscheint dann. Dies kann man dann mit VBA verwenden. Also kann man auch mit VBA die Formel eintragen und dann das Ergebnis abgreifen. Die Formel kann zum Schluss wieder gelöscht wreden.

Beispiel:
Im Ordner C:\Eigene Dateien befindet sich die Datei DBAdressen.xlsx. Aus dieser Datei sollen vom Blatt Allgemein die Daten aus A1 bis A25 in eine Listbox eingelesen werden, ohne diese Datei zu öffnen.

Const strBezug As String = "='C:\Eigene Dateien\[DBAdressen.xlsx]Allgemein'!$A$" 'Folgende Scheife schreibt nacheinander die Formel in B2 und fügt der Listbox 'dann das Ergebnis der Formel hinzu. Private Sub CommandButton1_Click() Dim intI As Integer ListBox1.Clear Application.DisplayAlerts = False For intI = 1 To 25 Range("B1").Formula = strBezug & intI If Not IsError(Range("B1")) Then ListBox1.AddItem Range("B1").Text Next Range("B1") = "" Application.DisplayAlerts = True End Sub

Das Ganze kann (und sollte) natürlich mit Errorhandlings verfeinert werden.

In einer Tabelle stehen in einer Spalte (hier: D) untereinander verschiedene Produktnamen. Bei jedem Produkt stehen rechts daneben (hier: E, F und G) die Preise für drei verschiedene Länder.

Die Preise bei jedem Produkt sollen automatisch farbig markiert werden: der günstigste grün, der höchste rot und ansonsten gelb.

Lösung

Die Aufgabe wird mit Bedingte Formatierung im Ribbon Start erfüllt.

Dazu müssen für jede der drei Preisspalten jeweils drei Regeln erstellt werden - eben eine für den günstigsten, eine für den höchsten und eine für den restlichen Preis. Wir fangen an mit der ersten Spalte, in der Preise enthalten sind, hier also E2:E19 und markieren diese. Anschlie�end rufen wir die bedingte Formatierung auf und wählen dort Neue Regel.

In der Auswahlliste wählen wir den Punkt Nur Zellen formatieren, die enthalten. Dies ist die Basis für die weiteren Formatierungen.

Grüne Formatierung

Die grüne Formatierung soll erscheinen, wenn der Preis am niedrigsten ist. Hier bietet sich also die Funktion MIN zum Vergleich mit den anderen beiden Preisen an. Wir stellen also beim Zellwert auf kleiner als und tragen rechts ein: =MIN(F2:G2). Mit dem Button Formatieren legen wir die grüne Hintergrundfarbe fest.

Mit OK übernehmen wir die Regel.

Rote Formatierung

Mit rotem Hintergrund soll gekennzeichnet werden, wenn der Preis am höchsten ist. Dazu ist die Funktion MAX sinnvoll.

Wir bleiben also im Dialog und wählen Neue Regel. Anschlie�end führen wir die gleichen Schritte wie bei der grünen Formatierung durch, nur eben mit grö�er als, MAX und der roten Formatierung.

Gelbe Formatierung

Einen gelben Hintergrund soll die Zelle bekommen, wenn der Preis nicht am höchsten und nicht am niedrigsten ist, wenn er also zwischen den beiden anderen Preisen liegt.

Wir bleiben weiterhin im Dialog und wählen wieder Neue Regel. Wenn wir nun wieder Nur Zellen formatieren, die enthalten anklicken, müsste beim Zellwert schon zwischen ausgewählt sein - wenn nicht, dies nachholen.

In die Felder schreiben wir jeweils =F2 und =G2. Gelben Hintergrund auswählen und bestätigen. Nun müssten die drei Regeln im Manager angezeigt werden.

Wenn nun Zahlen eingetragen werden, sollte das beim ersten Land funktionieren, die Hintergründe müssten automatisch entsprechend der Zahlen formatiert werden. Allerdings müssen die Schritte noch für die anderen beiden Länder wiederholt werden. Beim mittleren Land aufpassen; die Zellen in MIN und MAX müssen hier mit Semikolon getrennt werden, weil es keine Bis-Bereiche sind, sondern auseinanderliegende Zellen.

Auswertung: Matrixformel

Unter der Tabelle sollen nun noch zu jedem Land die grünen, roten und gelben Zellen gezählt werden. Das Problem: Mit einer reinen Formellösung können keine farbigen Zellen gezählt werden.

Ein Lösungsansatz ist, zu zählen, wie viele Zellen in der jeweiligen Spalte grö�er bzw. kleiner als die Zellen daneben sind. Damit wir nicht jede Zeile einzeln berücksichtigen müssen, verwenden wir dazu eine Matrixformel, die den Bereich einer Spalte über alle Zeilen hinweg erfasst.

Hinweis: Die geschweiften Klammern nicht eingeben, sondern die Eingabe der Formel mit Strg + Umschalt + Enter abschlieÃ?en. Damit erscheinen die geschweiften Klammern automatisch. Ab Excel 365 sind die geschweiften Klammern nicht mehr notwendig.

Wir beginnen beim ersten Land, hier mit der Formel in E24. Es sollen die Zellen gezählt werden, die in den Zeilen die niedrigsten Preise haben. Dabei zählen wir aber nicht, sondern wir addieren für jede dieser niedrigsten Zellen die 1. Wir bilden also die Summe, hier das Grundgerüst:

=SUMME( wenn Zahl in der Zeile am niedrigsten ; dann addiere 1; sonst addiere 0))

Wir verwenden hier diese Logik (andere Varianten gibt es natürlich auch):

Wenn die Zahl beim Land 1 (E) kleiner als die Zahl beim Land 2 (F) ist, dann wenn die Zahl beim Land 1 (E) auch kleiner als die beim Land 3 (G) ist, dann addiere 1, sonst 0, sonst 0.

Wir addieren hier also zweimal 0 - einmal für die erste Bedingung (Land 1 nicht kleiner als Land 2) und einmal für die zweite Bedingung (Land 1 nicht kleiner als Land 3).

Diese Struktur bauen wir in die Formel ein, so dass die (an Strg + Umschalt + Enter denken!) nun so aussieht:

{=SUMME(WENN(E2:E19<F2:F19;WENN(E2:E19<G2:G19;1;0);0))}

Damit haben wir die Anzahl der grünen Zellen beim Land 1 in E24. Die gleiche Formel kommt zu den anderen Ländern. Vorsicht, die kann aber nicht gezogen werden, weil die Zellen in den Formeln angepasst werden müssen.

Ebenfalls die gleiche Formel, nur mit dem grö�er als >, kommt in die Zeile 26, wo die roten Zellen gezählt werden:

{=SUMME(WENN(E2:E19>F2:F19;WENN(E2:E19>G2:G19;1;0)))}

Bei den gelben Zellen geht es einfacher - einfach alle Zellen in der Spalte zählen und die grünen und roten subtrahieren:

=ANZAHL(E2:E19)-E24-E26

Eine Beispieldatei mit dieser Lösung: 326_preisvergleiche.xlsx

Basis: In Spalte A (im Code also 1) stehen aufsteigend sortiert und ohne leere Zellen dazwischen die Zeiten.

Zum Umsetzen der Aufgabenstellung benötigen wir vier einzelne Routinen. Diesen müssen wir zwei Variablen zur Verfügung stellen, die deshalb ganz am Anfang des Moduls deklariert werden müssen:

Private lngZ As LongPtr Private bolC As Boolean

In der ersten Routine Start() löschen wir die Zellinhalte neben den Uhrzeiten und legen in lngZ die erste Zeile mit einer Uhrzeit fest. Dazu setzen wir bolC auf True. Das Makro, das später die Eintragungen vornimmt, wird diese Variable prüfen und den Vorgang nur fortsetzen, wenn diese auf True gesetzt ist. Anschlie�end starten wir den Vorgang mit der Sub ZeitFestLegen():

Sub Start() lngZ = 2 'erste Zeile mit Uhrzeit Range(Cells(lngZ, 2), Cells(Rows.Count, 3)).ClearContents bolC = True ZeitFestLegen End Sub

Die Sub ZeitFestLegen() hat nur folgende Aufgaben:

• prüfen, ob in Spalte A der Zeile lngZ eine Zeit steht,
• prüfen, ob bolC noch True ist,
• wenn zweimal ja: festlegen, dass bei der Zeit in Zeile lngZ etwas passiert (das Makro Eintragen ausführen) - zunächst ist das hier die Zeit in Zeile 2, weil wir die bereits in lngZ festgelegt haben:

Sub ZeitFestLegen() Dim datZeitAngabe As Date If Cells(lngZ, 1).Value = "" Then Exit Sub If bolC = False Then MsgBox "Makro wurde angehalten" Exit Sub End If datZeitAngabe = Cells(lngZ, 1).Value Application.OnTime datZeitAngabe, "Eintragen" End Sub

Nun wei� der Code, wann etwas passieren soll, nämlich bei der ersten Zeit. Mit der Sub Eintragen() legen wir fest, was passieren soll. Hier werden einfache Texte in die Nachbarzellen eingetragen.

Nach dem Eintragen wird die Zeile lngZ um 1 erhöht und wieder die Sub ZeitFestLegen aufgerufen, die nun dafür sorgt, dass das Ganze bei der nun in der nächsten Zeile gefundenen Zeit von vorn losgeht - wenn eine Zeit in dieser neuen Zelle steht und bolC immer noch True ist:

Sub Eintragen() Cells(lngZ, 2).Value = "Hallo, Spalte B" Cells(lngZ, 3).Value = "Hallo, Spalte C" lngZ = lngZ + 1 ZeitFestLegen End Sub

Das sollte schon mal funktionieren. Aber: Es gibt für den Anwender keine Möglichkeit, das vorzeitig abzubrechen. Bei der nächsten Zeit wird eingetragen und sofort die neue Zeit festgelegt.

Deshalb sollten wir eine Möglichkeit einbauen, den Wert der Variablen bolC auf False zu setzen, damit die Sub ZeitFestLegen den Vorgang abbricht. Dazu reicht das Stück Code, das man vielleicht an einen Button o. ä. binden kann:

Sub Stoppen() bolC = False End Sub

Um Chartereignisse nutzen zu können, muss man vorher ein Klassenmodul erstellen. Dieses ist im Gegensatz zu den Application-Ereignissen jedoch recht einfach zu bewerkstelligen.

Anlegen des Klassenmoduls:

Gehen Sie im Visual-Basic-Editor auf Einfügen ▸ Klassenmodul und geben folgendes ein:

Public WithEvents Diagramm As Chart

Um alles übersichtlicher zu gestalten, sollte man der Klasse den Namen Diagrammklasse geben (unter Eigenschaften/Namen). Nun stehen in der linken Drop-Down-Liste die neue Klasse und in der rechten die dazugehörigen Eigenschaften zur Verfügung. Damit die Ereignisprozeduren ausgeführt werden, muss man nun das Diagramm der Diagrammklasse zuordnen. Dies geschieht in einem beliebigen Modul durch folgenden Code:

Dim MeinDiagramm As New Diagrammklasse Sub DiagrammZuordnen() Set MeinDiagramm.Diagramm = Worksheets(1).ChartObjects(1).Chart End Sub

Parameter:

Wie man erkennen kann, übergeben auch die Chartereignisse Parameter. Da man nun allerdings eine Klasse definiert hat, sind diese derart vielfältig, dass sie hier nicht aufgeführt werden. Sie sind aber in der Online-Hilfe ausreichend erklärt.

Chartereignisse:

Diagramm_Activate

Tritt ein, wenn das Diagramm aktiviert wird. War das Diagramm vor einem Blattwechsel aktiv und wird wieder zu diesem Blatt gewechselt, tritt dieses Ereignis auch ein.

Diagramm_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)

Tritt bei einem Doppelklick auf.

Diagramm_BeforeRightClick(Cancel As Boolean)

Tritt beim drücken der rechten Maustaste ein.

Diagramm_Calculate

Tritt auf, wenn das Diagramm neu gezeichnet wird, z. B. neue Daten.

Diagramm_Deactivate

Tritt bei einem Blatt- oder Diagrammwechsel auf.

Diagramm_DragOver

Tritt ein, wenn ein Zellbereich über ein Diagramm gezogen wird, aber noch nicht losgelassen ist.

Diagramm_DragPlot

Tritt ein, wenn ein Zellbereich über ein Diagramm gezogen wurde und losgelassen wurde.

Diagramm_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal X As Long, ByVal Y As Long)

Tritt ein, wenn die Maustaste gedrückt wird.

Diagramm_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal X As Long, ByVal Y As Long)

Tritt ein, wenn die Maus bewegt wird.

Diagramm_MouseUp(ByVal Button As Long, ByVal Shift As Long, ByVal X As Long, ByVal Y As Long)

Tritt ein, wenn die Maustaste losgelassen wurde.

Diagramm_Resize

Tritt ein, wenn die Diagrammgrö�e verändert wurde.

Diagramm_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)

Tritt ein, wenn das Diagramm ausgewählt wurde.

Diagramm_SeriesChange(ByVal SeriesIndex As Long, ByVal PointIndex As Long)

Tritt bei einer Veränderung der ausgewählten Datenreihe ein.

Eigentlich gar nicht.

Man kann aber mit einer Formel Bezug auf eine geschlossene Mappe nehmen; deren Ergebnis erscheint dann. Dies kann man dann mit VBA verwenden. Also kann man auch mit VBA die Formel eintragen und dann das Ergebnis abgreifen. Die Formel kann zum Schluss wieder gelöscht wreden.

Beispiel:
Im Ordner C:\Eigene Dateien befindet sich die Datei DBAdressen.xlsx. Aus dieser Datei sollen vom Blatt Allgemein die Daten aus A1 bis A25 in eine Listbox eingelesen werden, ohne diese Datei zu öffnen.

Const strBezug As String = "='C:\Eigene Dateien\[DBAdressen.xlsx]Allgemein'!$A$" 'Folgende Scheife schreibt nacheinander die Formel in B2 und fügt der Listbox 'dann das Ergebnis der Formel hinzu. Private Sub CommandButton1_Click() Dim intI As Integer ListBox1.Clear Application.DisplayAlerts = False For intI = 1 To 25 Range("B1").Formula = strBezug & intI If Not IsError(Range("B1")) Then ListBox1.AddItem Range("B1").Text Next Range("B1") = "" Application.DisplayAlerts = True End Sub

Das Ganze kann (und sollte) natürlich mit Errorhandlings verfeinert werden.

Der Code benötigt eine Userform1, darauf eine TextBox1 und einen SpinButton1 (Drehfeld).

Für den Aufruf der Userform legen wir fest, dass das Maximum des SpinButtons gesetzt wird, hier 401768 für den 31.12.2999. Au�erdem soll in der TextBox das aktuelle Datum angezeigt und beim SpinButton der Wert des aktuellen Datums eingestellt werden:

Private Sub UserForm_Initialize() SpinButton1.Max = 401768 TextBox1.Value = Format(Now, "dd.mm.yyyy") SpinButton1.Value = CLng(CDate(TextBox1.Value)) End Sub

Dem SpinButton weisen wir zu, dass beim Betätigen dessen Wert im Datumsformat in die Textbox eingetragen wird:

Private Sub SpinButton1_Change() TextBox1.Text = Format(SpinButton1.Value, "dd.mm.yyyy") End Sub

Fehlt noch die TextBox - wenn dort ein gültiges Datum (Angaben durch Punkt oder Minus getrennt) eingetragen wird, soll der Wert dieses Datums dem SpinButton zugewiesen werden, damit bei dessen Betätigen in der Textbox das vorherige oder nächste Datum eingetragen wird:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) If Not IsDate(TextBox1.Text) Then MsgBox "Die Eingabe ist kein gültiges Datum." TextBox1.Text = Format(SpinButton1.Value, "dd.mm.yyyy") Exit Sub End If SpinButton1.Value = CLng(CDate(TextBox1.Text)) End Sub

Der Code zum Kopieren:

Private Sub UserForm_Initialize() SpinButton1.Max = 401768 TextBox1.Value = Format(Now, "dd.mm.yyyy") SpinButton1.Value = CLng(CDate(TextBox1.Value)) End Sub Private Sub SpinButton1_Change() TextBox1.Text = Format(SpinButton1.Value, "dd.mm.yyyy") End Sub Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) If Not IsDate(TextBox1.Text) Then MsgBox "Die Eingabe ist kein gültiges Datum." TextBox1.Text = Format(SpinButton1.Value, "dd.mm.yyyy") Exit Sub End If SpinButton1.Value = CLng(CDate(TextBox1.Text)) End Sub

Nostalgie - mehr ist das wohl heute nicht mehr. Oder nutzt noch jemand die alten Menüs in Excel?

Sub MenueErstellen() Dim objAktiveMenueLeiste As Object Dim objMeinMenue As Object, objBefehl As Object, objMB As Object 'objMB, objMeinMenue, Befehl und objAktiveMenüLeiste sind Variablen On Error Resume Next 'Eigene Menüleiste löschen, falls Makro nochmal aufgerufen wird: Application.CommandBars("MeinMenü").Delete 'Falls keine eigene Menüleiste erstellt wurde, sondern nur ein Menü: CommandBars.ActiveMenuBar.Controls("Mein Menü").Delete 'Menüleiste hinzufügen und einblenden 'Soll die aktive Menüleiste ersetzt werden, Hochkommas entfernen: 'Set objMB = CommandBars.Add(Name:="MeinMenü", MenuBar:=True) 'CommandBars("MeinMenü").Visible = True Set objAktiveMenueLeiste = CommandBars.ActiveMenuBar 'Menü erstellen Set objMeinMenue = objAktiveMenueLeiste.Controls.Add(Type:=msoControlPopup, Temporary:=True) objMeinMenue.Caption = "&Mein Menü" 'Anstelle Makroname den Makro einsetzen 'Erster Befehl im Menü Set objBefehl = objMeinMenue.Controls.Add(Type:=msoControlButton, ID:=1) With objBefehl .Caption = "&1. Befehl" .OnAction = "Makroname" End With 'Zweiter Befehl im Menü Set objBefehl = objMeinMenue.Controls.Add(Type:=msoControlButton, ID:=1) With objBefehl .Caption = "&2. Befehl" .OnAction = "Makroname" End With End Sub 'Um die Original-Menüleiste wieder einzublenden kann man 'folgendes Makro verwenden: Sub EigeneMenueLeisteLoeschen() On Error Resume Next 'Löscht die selbsterstellte Menüleiste: Application.CommandBars("MeinMenü").Delete 'Löscht nur das Menü CommandBars.ActiveMenuBar.Controls("Mein Menü").Delete End Sub

Excel-Kommunikator: Chat und E-Mail per Excel

Das Add-In ist der Nachfolger des Mail-Add-Ins, an dem immer wieder Interesse bestand. Allerdings war es mit der Zeit veraltet, so dass eine komplette Neuentwicklung langsam überfällig war. Das hier vorgestellte Add-In sollte ab der Version 2007 funktionieren - Nutzer des alten Add-Ins finden hier nur eine andere (und umfangreichere) Bedienung; die alten Daten können mit der gleichen Syntax übernommen werden.

Mit diesem Chat- und Mail-Add-In ist es möglich, Gespräche zu führen, Bereiche aus Exceltabellen auszutauschen und Mails mit Anhängen zu senden. Es ist also ein schneller Informationsaustausch gewährleistet, ohne zu einem anderen Programm wechseln zu müssen. Der Austausch erfolgt dabei überwiegend im Menüband in eigenen Ribbon-Tabs. Bei den Informationen kann es sich um einfache Postings handeln, um Inhalte von Zellen oder Bereichen (Werte oder Formeln) oder um Mails mit Anhängen. So ist es zum Beispiel möglich, die aktuelle Mappe per Mausklick an mehrere Empfänger zu senden oder Tabellenbereiche an andere Nutzer zu übermitteln.

Basis ist ein eigener Nick, der frei gewählt werden kann, jedoch insgesamt nur einmal vorkommen darf. Nach Gro�-/Kleinschreibung wird dabei unterschieden. Dieser Nick muss einmalig im System registriert werden, danach erfolgt die Kommunikation pro Add-In immer mit diesem Nick. Jeder Teilnehmer sieht Sie dann also unter diesem Namen. Die Absenderangaben bei Mails können natürlich unabhängig davon verwendet werden, so dass ein Empfänger Ihren richtigen Namen sieht, wenn Sie diesen beim Mailversand angeben.

Chat

Eigener Nickname

Mit diesem sind Sie überall sichtbar. Eintragen, Frage nach Registrierung mit »Ja« beantworten, fertig. Pro Add-In wird ein Name vergeben; Sie können also die Add-In-Datei auch an einem anderen Arbeitsplatz verwenden.

Gruppen

Sie haben die Möglichkeit, sich in verschiedenen Gruppen zu unterhalten. Die Gruppe »offen« ist voreingetragen, hier kann jeder ohne Kennwort lesen und schreiben.

Möchten Sie eine Gruppe hinzufügen, klicken Sie auf »Gruppe hinzufügen« und tragen Sie anschlie�end im Dialog den gewünschten Gruppennamen ein. Hier gibt es zwei Möglichkeiten:

Existiert der Gruppenname bereits, können Sie das Kennwort eintragen und so der Gruppe beitreten.
Existiert der Gruppenname noch nicht, können Sie ein Kennwort vergeben und so Ihre eigene Gruppe erstellen. Den Namen und das Kennwort können Sie dann ausgewählten Personen geben, damit diese auf diesem Weg dieser Gruppe beitreten können. So können Sie sich mit einem begrenzten Personenkreis unterhalten, zum Beispiel einer Mitarbeitergruppe.

Im Gruppe-Auswahlfeld wählen Sie ganz einfach, welche Postings Sie lesen und wo Sie schreiben möchten.

Posten und Listenanzeige

Ihren Text geben Sie nach der Wahl der gewünschten Gruppe einfach in »Ihre Msg« ein und senden ihn mit »Enter« ab. In der Liste »Msgs« sehen Sie die aktuellen Beiträge chronologisch rückwärts, also den aktuellsten Beitrag ganz oben.

Zellen und Bereiche posten

Sie können nicht nur eigene Postings schreiben, sondern auch Inhalte aus der Tabelle posten.

Was dabei übermittelt wird, hängt vom Kästchen »Formel« ab - ist es deaktiviert, werden die Werte der Zellen übermittelt (im Beispiel â??-06:30â??), sonst die Formeln, falls welche eingetragen sind (sonst auch die Werte).

Klicken Sie auf »Zelle posten«, wenn Sie den Inhalt der gerade aktiven Zelle posten möchten. Dieser erscheint dann wie gehabt in der Liste der Postings.

Markieren Sie einen Bereich und wählen Sie »Bereich posten«, wenn Sie einen kompletten Tabellenbereich übermitteln möchten. In diesem Fall erscheint in den Postings nicht der Inhalt des Bereiches, sondern der Hinweis, dass es sich um einen Tabellenbereich handelt und um welchen Bereich. Dies ist wichtig, um diesen Inhalt später wieder an der richtigen Stelle einfügen zu können.

Ausgaben

Postings können auch in Tabellen übernommen werden. Wichtig ist dafür, dass das gewünschte Posting in der Liste gewählt und der Zielbereich ab der aktiven Zelle frei ist - bereits vorhandene Zellinhalte werden sonst überschrieben.

Mit »Posting in Zelle« wird das gewählte Posting in die aktive Zelle eingetragen, wie es in der Liste zu sehen ist. Ausnahme ist dabei ein Tabellenbereich - handelt es sich beim gewählten Posting um einen solchen, wird der Bereich entsprechend des Quellbereiches ab der aktiven Zelle eingetragen. Sind Formeln enthalten, ist es sinnvoll, dass die aktive Zelle die ist, die im Beginn des Tabellenbereiches angegeben ist, damit die Bezüge in den Formeln stimmen. Im Beispiel aus dem Screenshot sollte also die aktive Zelle E1 sein, wenn das Posting mit dem Bereich eingefügt wird.

Formatierungen werden nicht übernommen, eingetragene Bereiche müssen also ggf. nachformatiert werden. Dies betrifft insbesondere Datums- und Zeitformate.

Mit »Postingliste in Tabelle« werden die Postings der gewählten Gruppe einfach in eine neu erstellte Tabelle eingetragen.

Neue Nachrichten

Aus Performancegründen werden neue Postings von anderen Nutzern nicht sofort angezeigt.

Die Aktualisierung der Beiträge in einer Gruppe erfolgt über den Button »Refresh«.

Sollen die Beiträge einer Gruppe automatisch aktualisiert werden, aktivieren Sie »Autofrefresh [an|aus]«. Damit werden die Beiträge alle 15 Sekunden abgerufen und in der Liste aktualisiert. Im Falle eines neuen Postings wird der Chat-Tab automatisch aktiviert und es erscheint ein Hinweissymbol »Neues Posting!«.
Haben Sie »Sprachnachricht« aktiviert, wird das neue Posting vorgelesen.

Mail

Voraussetzung für die Nutzung ist, dass in »Chat« der Nickname registriert wurde.

Absender

Die Absenderangaben werden einfach eingetragen und bleiben bis zur nächsten �nderung gespeichert. Da das Add-In keine Mails empfangen kann, sollte die Absenderadresse eine solche sein, an die der Empfänger antworten kann.

Empfänger

Zur Eingabe der Empfänger gibt es mehrere Möglichkeiten.

Die Mailadressen können direkt in das Eingabefeld eingetragen werden, mehrere durch Semikolon getrennt.

Im Bereich »Zusammenstellen« kann zwischen Add-In- und Outlookkontakten umgeschaltet werden. Im Anschluss wird der entsprechende Eintrag gewählt und mit »Zu Empfängern hinzufügen« in das Feld für die Empfänger übernommen.

Die Liste der Add-In-Kontakte wird über den kleinen Dialoglauncher unten rechts zur Bearbeitung aufgerufen.

Mailtext, Anhang

Mailtext und Anhang beim Versand einer externen Datei werden in den jeweiligen Dialogen eingegeben bzw. gewählt, die nach Klick auf »Mailtext« bzw. »Datei« erscheinen. Wichtig beim Versand des aktiven Blattes ist, dass das Add-In dort gespeichert ist, wohin das Add-In eine temporäre Datei speichern kann.

Die Dateigrö�e des Anhangs ist derzeit auf 500 KB begrenzt.

Versand

Mit Klick auf die entsprechende Schaltfläche erfolgt der Versand, bei Erfolg erscheint eine Meldung. Bei grö�eren Dateien kann dies natürlich auch einen Moment dauern.

Backup und Restore

Backup

Natürlich kann der Fall eintreten, dass das Add-In einmal neu installiert werden muss - sei es wegen eines Updates oder wegen eines Datenverlusts. Handelt es sich dann um eine neue Add-In-Datei, wäre der Nick nicht mehr zugänglich, weil ja u. a. die Daten dazu weg wären.

Für diesen Fall (und beabsichtigte Erweiterungen) ist es möglich, ein Backup auf dem Server zu erstellen. Dazu dient der Button »Sicherung«, der dann sichtbar ist, wenn ein Nick eingerichtet wurde. Damit werden alle Einstellungen des Add-Ins gesichert.

Beim ersten Erstellen eines Backups erhalten Sie ein Kennwort, das unbedingt aufbewahrt werden muss. Ansonsten können Sie nach Belieben Backups erstellen - einfach auf den Button klicken und bestätigen.

Restore

Bei einer leeren Add-In-Datei, wenn also noch kein Nick vergeben ist, ist der Button »Restore« statt des Buttons zur Sicherung sichtbar. Nach dem Anklicken erscheint ein Dialog zum Eingeben des Nicknamens und des Kennworts, das Sie beim ersten Erstellen vergeben haben. Stimmen diese Daten, werden die Daten aus dem letzten Backup in das Add-In eingetragen und es stehen alle Funktionen/Daten wie beim Zeitpunkt des Erstellens des Backups zur Verfügung.

Technik, Ausschlüsse und Datenschutz

Da die Daten entfernten Empfängern zur Verfügung gestellt werden, müssen sie natürlich auch irgendwo gespeichert werden. Dies erfolgt auf dem Server joerglorenz.de in einer MySQL-Datenbank, die auf dem aktuellen Stand der Technik abgesichert ist. Au�er mir, dem Autor des Add-Ins, hat niemand Zugriff darauf.

Gespeichert werden nur die Daten, die den Absendern und den Empfängern vorgehalten werden müssen - also all das, was Sie über die Felder im Menüband eingeben. Dateien als Mailanhänge werden auf dem Server nur bis zum letztendlichen Versand der Mail gespeichert und anschlie�end gleich wieder gelöscht. Da das ganze System über Nicknamen läuft, werden auch keine sonstigen personenbezogenen Daten gespeichert - natürlich mit Ausnahme derer, die Sie selbst in den Eingabefeldern/Messages eintragen.

Es wird zugesichert, dass Ihre Daten nur zum eigentlichen Zweck Ihrer Kommunikation gespeichert werden und sonst in keiner Weise verwendet oder überwacht werden. Au�er natürlich, wenn ich selbst davon nichts wei� - ausschlie�en kann man ja leider nichts.

Sollen bestimmte Daten gelöscht werden, wenden Sie sich bitte einfach mit Angabe des Nicknamens an mich.

Verboten sind generell Texte, die gegen Recht und Gesetz versto�en, aber auch fanatische und insbesondere rechte bzw. fremdenfeindliche Inhalte. Sollten solche Texte bemerkt werden, wenden Sie sich bitte an mich. Diese werden dann durch mich unverzüglich gelöscht.

Kosten

Dieses Add-In kann leider nicht kostenlos angeboten werden. Sie haben die Möglichkeit, 50 Postings abzusetzen und 50 Mails (an mehrere Empfänger gilt als eine Mail) zu versenden. Eine weitere Nutzung ist dann für 10,00 � pro Add-In (also pro Nick) möglich. Bei Nutzung in Gruppen (Unternehmen, Vereine usw.) kann es verschiedene Ermä�igungen geben, wenn mir eine Liste der zugehörigen Nicks überlassen wird.

Bei Angabe einer Rechnungsanschrift erhalten Sie natürlich in jedem Fall eine absetzbare Rechnung.

Perspektivisch wird das Add-In um weitere Funktionen ergänzt. Diese Erweiterungen sind dann natürlich im Preis bereits enthalten.

Download: excelkomm.xlam

Dafür gibt es verschiedene Möglichkeiten - wobei es mit der Find-Methode Probleme gibt, da die nach dem Datum sucht, nicht aber nach einem Teil davon. Gezeigt werden zwei Möglichkeiten - die erste funktioniert ab Excel 365, die zweite generell:

Einsatz der Tabellenfunktion FILTER() ab Excel 365

Das grundlegende Vorgehen ist hier wie bei der älteren Variante (siehe unten), nur beim Sammeln der Daten wird die Tabellenblattfunktion FILTER() verwendet. Die Blätter werden also auch einzeln abgearbeitet, pro Blatt arbeitet aber diese Funktion. Wir gehen hier davon aus, dass der Datenbereich ab Zeile 3 und ab Spalte C beginnt.

Die Funktion sorgt dafür, dass nicht jede Zeile auf dem Blatt durchlaufen werden muss, sondern dass bereits eine Ergebnismenge vorhanden ist - es müssen also weniger Daten ausgewertet werden. Sie liefert einen Array, in dem die Zeilen enthalten sind, in denen sich die gefundenen Geburtstage befinden. Die einzelnen Daten sind Elemente jeder Zeile, der Geburtstag ist das vierte Element (ab Spalte C). Die Datumsangaben liegen in diesem Array als Long-Zahlen vor, deshalb sind sie hier noch formatiert.

Für den praktischen Einsatz kann es notwendig sein, die Blätter noch etwas einzugrenzen, weil sonst Fehler auftreten könnten. Hier wird mit IsArray() geprüft, ob ein Array vorliegt, fürs Grobe reicht das erst mal. Die Routine:

Sub Geburtstage1() Dim lngLZ As LongPtr Dim intN As Integer Dim strEintrag As String Dim wksBlatt As Worksheet Dim arrS(), lngArrS As LongPtr Dim varT lngArrS = -1 For Each wksBlatt In ActiveWorkbook.Sheets lngLZ = wksBlatt.Cells(wksBlatt.Rows.Count, 4).End(xlUp).Row varT = Application.Evaluate("=FILTER(" & wksBlatt.Name & "!C3:J" & lngLZ & ",(DAY(" & wksBlatt.Name & "!F3:F" & lngLZ & ")=DAY(TODAY()))*(MONTH(" & wksBlatt.Name & "!F3:F" & lngLZ & ")=MONTH(TODAY())),0)") If IsArray(varT) Then For intN = 1 To UBound(varT) lngArrS = lngArrS + 1 ReDim Preserve arrS(lngArrS) strEintrag = Format(varT(intN, 4), "DD.MM.YYYY") & " " strEintrag = strEintrag & Format(Year(Date) - Year(varT(intN, 4)), "00") & " " strEintrag = strEintrag & varT(intN, 3) & ", " & varT(intN, 2) strEintrag = strEintrag & ", Telefon: " & varT(intN, 8) strEintrag = strEintrag & " (Blatt: " & wksBlatt.Name & ")" arrS(lngArrS) = strEintrag Next End If Next UserForm1.ListBox1.List = arrS UserForm1.Show End Sub

Alte Variante, generell funktionshähig

Hier wird das Verwenden einer Schleife aufgezeigt.

Dazu wird natürlich eine Userform benötigt, hier ist es UserForm1. Auf ihr sind die ListBox1 und ein Commandbutton zum Schlie�en der Userform.

Die folgende Routine, die hier in einem Standardmodul sein muss, durchläuft alle Blätter und prüft in Spalte 6 (F) auf Datumsangaben. Wenn es sich um ein Datum handelt, wird auf �bereinstimmung mit dem heutigen Tag und dem heutigen Monat geprüft. Gibt es die, wird in diesem Beispiel einfach ein String aus den Angaben gebastelt und in einen Array aufgenommen.

Am Ende wird der Array mit UserForm1.ListBox1.List an die Listbox übergeben und die Userform aufgerufen.

Sub Geburtstage() Dim lngEZ As LongPtr, lngLZ As LongPtr, lngZ As LongPtr, lngS As LongPtr Dim strEintrag As String Dim wksBlatt As Worksheet Dim arrS(), lngArrS As LongPtr lngS = 6 lngArrS = -1 For Each wksBlatt In ActiveWorkbook.Sheets lngEZ = wksBlatt.UsedRange.Row lngLZ = lngEZ + wksBlatt.UsedRange.Rows.Count If lngLZ > lngEZ Then With wksBlatt For lngZ = lngEZ To lngLZ If IsDate(.Cells(lngZ, lngS)) Then If Day(.Cells(lngZ, lngS)) = Day(Date) And Month(.Cells(lngZ, lngS)) = Month(Date) Then lngArrS = lngArrS + 1 ReDim Preserve arrS(lngArrS) strEintrag = .Cells(lngZ, 6).Text & " " strEintrag = strEintrag & Format(Year(Date) - Year(.Cells(lngZ, 6)), "00") & " " strEintrag = strEintrag & .Cells(lngZ, 5) & ", " & .Cells(lngZ, 4) strEintrag = strEintrag & ", Telefon: " & .Cells(lngZ, 10) strEintrag = strEintrag & " (Blatt: " & .Name & ")" arrS(lngArrS) = strEintrag End If End If Next End With End If Next UserForm1.ListBox1.List = arrS UserForm1.Show End Sub

Es gibt natürlich auch Alternativen. So kann z. B. der Array mehrspaltig verwendet und das Ganze zu einer Funktion umgeschrieben werden, die den Array zurückgibt und beliebig eingesetzt werden kann. Oder die Listbox wird mit ColumnCount mehrspaltig erstellt und die einzelnen Daten werden auf mehrere Spalten verteilt. Oder die Daten werden sofort in die Listbox geschrieben. Oder, oder, oder �

Soll die Geburtstagsprüfung beim Dateiaufruf erfolgen, wird im Klassenmodul DieseArbeitsmappe (doppelt anklicken) dieser Code eingefügt:

Private Sub Workbook_Open() Geburtstage End Sub

Damit der Commandbutton wirkt: Doppelklick auf den Button und diesen Code eintragen:

Private Sub CommandButton1_Click() Unload Me End Sub

imageMSO-Galerie

Das Add-In stellt im Menüband jeweils eine Galerie der verfügbaren Symbole zur Word-UI oder zur imageMSO-Tabelle von Microsoft zur Verfügung. Je nach Galerie sind die Symbole auf mehrere Seiten aufgeteilt, die durch ein Dropdown gewählt werden können.

Die Symbole sind in einer einheitlichen GröÃ?e dargestellt. An der Schärfe erkennt man allerdings bereits, ob sie für â??largeâ??- oder â??normalâ??-Steuerelemente verwendet werden können.

Nach dem Anklicken eines Symbols erscheint rechts auf einem Button die Vergrö�erung und in einer Textbox der Code, der im XML verwendet werden kann. Rechts anklicken, kopieren und einfügen.

Da die IDs relativ aussagekräftige Bezeichnungen sind, ist eine Suchfunktion eingebaut. So kann gezielt nach Icons gesucht werden.

Das Add-In sollte ab Excel 2010 funktionieren. Es kann frei genutzt, darf aber nicht geändert werden.

Download: imagemso.xlam

Dazu nutzen wir drei verschiedene Routinen. Da der Vorgang aber immer weiter läuft, müssen wir dafür sorgen, dass wir den notfalls abbrechen können. Dazu deklarieren wir eine Variable am Anfang des Moduls:

Private bolC As Boolean

Die erste Sub ZeitFestLegen setzt diese Variable auf True und sorgt dafür, dass es in einer Sekunde losgeht, dass dann nämlich die Routine Eintragen gestartet wird:

Sub ZeitFestLegen() Dim datZeitAngabe As Date bolC = True datZeitAngabe = Time + TimeSerial(0, 0, 1) Application.OnTime datZeitAngabe, "Eintragen" End Sub

Diese Routine Eintragen trägt die Zeit in B2 ein und ruft, wenn bolC immer noch True ist, wieder ZeitFestLegen auf, die dafür sorgt, dass sich das Ganze nach einer Sekunde wiederholt:

Sub Eintragen() Range("B1") = Time If bolC = True Then ZeitFestLegen End Sub

Und so wird die Zeit eingetragen und eingetragen und eingetragen und â?¦

Was aber, wenn wir das nicht mehr wollen, also den Vorgang anhalten möchten? Dafür nehmen wir eine kleine Sub, die die Variable bolC auf False setzt. Damit wird dann keine neue Zeit festgelegt, der Vorgang ist beendet.

Sub Notstopp() bolC = False End Sub

Die letzte Routine kann zum Beispiel an einen Button gebunden werden.

Dazu wird der X-Button (also Schlie�en-Button) zwar nicht ausgeblendet, aber das Dialogfeld lä�t sich darüber nicht schlie�en. Damit ist auch Alt + F4 entschärft und die Userform lässt sich nur mit einer eigenen Schaltfläche schlie�en, die zweckmä�igerweise erst eingebaut werden sollte. Das funktioniert auch, wenn beim eigenen Button Cancel und/oder Default auf True gesetzt sind.

Das Makro bei den Userform-Ereignissen einfügen (Doppelklick auf Userform im Editor):

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = 0 Then Cancel = 1 MsgBox "Bitte verlassen Sie das Dialogfeld mit den Schaltflächen.", vbOKOnly + vbInformation, "Bitte Schaltfläche betätigen." End If End Sub

Hinweis zu Excel 2007: In diesem Fall wird im Ribbon Add-Ins ein eigener Bereich erstellt..

Sub Erstellen() Toolbars(1).ToolbarButtons.Add Button:=211 With Toolbars(1).ToolbarButtons(Toolbars(1).ToolbarButtons.Count) .OnAction = "MeinMakro" .Name = "Mein Makro ausführen!" End With End Sub Sub Loeschen() Toolbars(1).ToolbarButtons(Toolbars(1).ToolbarButtons.Count).Delete End Sub

Im VBA-Editor die UserForm anklicken, dann im Eigenschaftenfenster hinter Caption klicken und den Text eingeben, der in der Titelleiste erscheinen soll. Oder:

Private Sub CommandButton2_Click() Me.Caption = "Mein Titel" End Sub

Ausschlaggebend ist hier intTopPosition. Damit wird die erste Position auf 20 gesetzt und anschlie�end für jede Box erhöht - damit gibt man also die Position der Box vor. Wenn es zweispaltig werden soll, muss entsprechend bei einem bestimmten Schleifendurchlauf wieder auf 20, aber auch die Left-Position nach rechts gesetzt werden.

Private Sub CommandButton1_Click() Dim ctrCheck As Control Dim datStartDatum As Date Dim datZaehler As Date Dim intTopPosition As Integer Dim strMonat As String strMonat = "Februar" datStartDatum = CDate("1." & strMonat & ".2010") intTopPosition = 20 datZaehler = datStartDatum Do While Month(datZaehler) = Month(datStartDatum) Set ctrCheck = Me.Controls.Add("forms.CheckBox.1") ctrCheck.Top = intTopPosition ctrCheck.Caption = Format(datZaehler, "dd.MM.yy") Set ctrCheck = Nothing intTopPosition = intTopPosition + 15 datZaehler = datZaehler + 1 Loop End Sub

Die Eigenschaften der neu erstellten Steuerelemente kann man z. B. wie folgt aufrufen:

Private Sub CommandButton2_Click() MsgBox UserForm1.Controls("CheckBox5").Value End Sub

Auch das Kontextmenü in Excel wird nun per RibbonX angesprochen. Hier ist eine sehr gute Anleitung: RibbonX-Workshop - Das Kontextmenü.

Der alte Code:

Sub Kontext() Dim cbnNeuesMenue As CommandBarButton With CommandBars("Cell") Do While .Controls.Count > 0 On Error Resume Next .Controls(1).Delete Loop Set cbnNeuesMenue = .Controls.Add(msoControlButton) With cbnNeuesMenue .Caption = "&Signalton" .OnAction = "Ton" End With End With End Sub