beraten
programmieren
implementieren
trainieren
Ereignisse von Excel Objekten
Allgemein
Objekte besitzen nicht nur Eigenschaften und Methoden, sie sind auch in der Lage sogenannte Ereignisse (Events) auszulösen. Was versteht man nun unter einem Ereignis bzw. dem Auslösen eines Ereignisses.
Ereignisse sind meist Reaktionen eines Objektes auf Änderungen einer Eigenschaft. Stellen Sie sich vor, Sie empfinden auf einmal das Gefühl von Durst, d. h. Ihre Eigenschaft BinDurstig wird von False auf True gesetzt. Als Konsequenz dieser Änderung rufen Sie aus: "Mensch, hab ich einen Durst!". Sie geben damit Ihrer Umgebung bekannt, dass Sie nun durstig sind.
Jetzt gibt es zwei Möglichkeiten: Möglichkeit1 ist, dass dieser Ausruf von Ihnen für niemanden von Interesse ist, d. h. Ihr Ausruf verhallt ungehört. Möglichkeit2 ist, dass eine andere Person nur darauf gewartet hat, dass Sie durstig werden und Ihnen nun ein Glas Wasser bringt.
Die ereignisorientierte Programmierung befasst sich mit der zweiten Möglichkeit. Auf ein ausgelöstes Ereignis (der Ausruf, dass Sie durstig sind) wird reagiert, indem in einer ereignisbehandelnden Prozedur Anweisungen ausgeführt werden (man bringt Ihnen ein Glas Wasser).
Im VBA-Editor sehen Sie im Projekt-Explorer (Ansicht - Projektexplorer) unter dem Verzeichniseintrag Excel-Objekte einen Eintrag für die Arbeitsmappe und je einen Eintrag für jedes Tabellen- bzw. Diagrammblatt. Klicken Sie doppelt auf einen dieser Einträge, so öffnen Sie das zum Objekt gehörige Modul. Diese Module werden als die Klassenmodule des jeweiligen Objektes bezeichnet und bieten die Möglichkeit, auf unterschiedliche Ereignisse des jeweiligen Objektes zu reagieren.
Ereignisse der Arbeitsmappe
Öffnen Sie im VBA-Editor das Klassenmodul des Arbeitsmappenobjekts. Im oberen Bereich des Klassenmoduls befinden sich zwei Auswahlfelder.
Wählen Sie im linken Feld den Eintrag Workbook wird automatisch folgende Prozedur eingefügt.
Private Sub Workbook_Open() End Sub
In dieser Prozedur können Sie Anweisungen implementieren, die ausgeführt werden, wenn die Arbeitsmappe geöffnet wird. Bevor wir dies tun, wollen wir kurz den Aufbau dieser Prozedur untersuchen.
- Ereignisbehandelnde Prozeduren sind als Sub-Prozeduren implementiert.
- Ihr Gültigkeitsbereich ist Private, also von außerhalb des Moduls nicht aufrufbar.
- Die Prozedurbezeichnung setzt sich zusammen aus dem Klassennamen des Objekts (Workbook), einem Unterstrich und dem Namen des Ereignisses (Open).
Beim Öffnen der Arbeitsmappe passiert nun folgendes: Die Arbeitsmappe löst das Ereignis Open aus, und es gibt nun jemanden, den dieser Umstand interessiert, nämlich die Prozedur Workbook_Open. Somit verzweigt die Codeausführung in diese Prozedur, und gibt uns die Möglichkeit, auf das Open-Ereignis zu reagieren.
Im folgenden Beispiel wird beim Öffnen der Arbeitsmappe eine Meldung mit Informationen zur Arbeitsmappe angezeigt.
'Meldung beim Öffnen der Arbeitsmappe
Private Sub Workbook_Open()
Dim ausgabe As String
ausgabe = "Dateiname: " & ThisWorkbook.Name & vbNewLine & "Anzahl Tabellenblätter: " & ThisWorkbook.Worksheets.Count
MsgBox ausgabe, , "Willkommen"
End Sub
Beim Öffnen der Arbeitsmappe sollte eine Meldung angezeigt werden.
Um andere User zu ärgern, könnten Sie folgende Prozedur implementieren:
'Arbeitsmappe beim Öffnen sofort wieder schließen
Private Sub Workbook_Open()
MsgBox "Die Datei wird wieder geschlossen!", , "Liebe Grüsse"
ThisWorkbook.Close False
End Sub
Sollten Sie obiges Beispiel tatsächlich ausprobiert haben, können Sie die Datei mit gedrückter Shift-Taste öffnen, um die Ausführung dieser Prozedur (und aller anderen) zu verhindern (die Makroausführung wird generell deaktiviert).
Ist im oberen Bereich des Klassenmoduls im linken Auswahlfeld Workbook ausgewählt, enthält das rechte Auswahlfeld alle Ereignisse, die die Arbeitsmappe auslösen kann. Nach Auswahl des gewünschten Ereignisses wird die entsprechende ereignisbehandelnde Prozedur automatisch erstellt.
Bevor eine Arbeitsmappe geschlossen wird, löst sie noch das BeforeClose-Ereignis aus. Die ereignisbehandelnde Prozedur nimmt ein Argument Cancel entgegen. Argumente von ereignisbehandelnden Methoden werden meist als Referenz entgegengenommen, d. h. über eine Änderung des Argumentwertes können Sie mit dem ereignisauslösenden Objekt kommunizieren.
Setzen Sie das Cancel-Argument der Prozedur Workbook_BeforeClose auf True, teilen Sie der Arbeitsmappe mit, dass das Schließen der Arbeitsmappe abgebrochen werden soll.
Im folgenden Beispiel fragen wir den User, ob er die Arbeitsmappe tatsächlich schließen will.
'Nachfrage vor dem Schließen der Arbeitsmappe
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If MsgBox("Wollen Sie tatsächlich schließen?", vbYesNo, "Frage") = vbNo Then
Cancel = True
End If
End Sub
Beim Einfügen eines neuen Tabellen- oder Diagrammblatts wird das Ereignis NewSheet ausgelöst. Die Prozedur Workbook_NewSheet nimmt ein Argument vom Typ Object entgegen, das eine Referenz auf das neue Tabellen- bzw. Diagrammblatt enthält. Mit Hilfe der Funktion TypeName können wir den tatsächlichen Objekttyp ermitteln, der über das Argument referenziert wird.
Im folgenden Beispiel prüfen wir, ob es sich um ein neues Tabellenblatt (Worksheet) handelt. Trifft dies zu, so wird der User nach einer Bezeichnung für das neue Blatt gefragt und die Eingabe des Users in der Name-Eigenschaft abgelegt.
'Tabellenblattbezeichnung gleich beim Einfügen festlegen
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim bezeichnung As String
If TypeName(Sh) = "Worksheet" Then
bezeichnung = InputBox("Name des neuen Tabellenblatts:", "Frage", Sh.Name)
Sh.Name = bezeichnung
End If
End Sub
Beachten Sie, dass obige Prozedur einen Fehler liefert, wenn der User keine Bezeichnung oder eine bereits vorhandene Tabellenblattbezeichnung eingibt.
Da das Argument Sh vom Typ Object ist, erhalten Sie keinerlei Unterstützung durch den VBA-Editor, wenn Sie weitere Eigenschaften des neuen Tabellenblatts setzen wollen. Um dies zu ermöglichen, deklarieren wir eine zusätzliche Variable vom Typ Worksheet. Im folgenden Beispiel tragen wir zusätzlich in den Zellen A1 und A2 den Benutzernamen und das Erstellungsdatum ein.
'Zusätzliche Variable vom Typ Worksheet
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim bezeichnung As String
Dim ws As Worksheet
If TypeName(Sh) = "Worksheet" Then
Set ws = Sh
bezeichnung = InputBox("Name des neuen Tabellenblatts:", "Frage", ws.Name)
With ws
.Name = bezeichnung
.Cells(1, 1) = "Erstellt von: " & Application.UserName
.Cells(2, 1) = "Erstellt am: " & Date
End With
End If
End Sub
Weitere Ereignisse finden Sie im rechten Auswahlfeld im oberen Bereich des Klassenmoduls.
Ereignisse des Tabellenblatts
Öffnen Sie im VBA-Editor das Klassenmodul des gewünschten Tabellenblattobjekts. Im oberen Bereich des Klassenmoduls befinden sich zwei Auswahlfelder.
Wählen Sie im linken Feld den Eintrag Worksheet wird automatisch folgende Prozedur eingefügt.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) End Sub
In dieser Prozedur können Sie Anweisungen implementieren, die ausgeführt werden, wenn sich die Auswahl in dem Tabellenblatt ändert. Das Argument Target gibt Ihnen eine Referenz auf jenen Bereich, der aktuell im Tabellenblatt ausgewählt ist.
Prinzipiell haben die Prozeduren den gleichen Aufbau wie bei dem Arbeitsmappenobjekt.
- Ereignisbehandelnde Prozeduren sind als Sub-Prozeduren implementiert.
- Ihr Gültigkeitsbereich ist Private, also von außerhalb des Moduls nicht aufrufbar.
- Die Prozedurbezeichnung setzt sich zusammen aus dem Klassennamen des Objekts (Worksheet), einem Unterstrich und dem Namen des Ereignisses (SelectionChange).
Im folgenden Beispiel wollen wir in der Statusleiste den Namen des Tabellenblattes und die Zellbezeichnung des ausgewählten Bereichs anzeigen. Um sicherzustellen, dass die Statusleiste sichtbar ist, setzen wir die Anwendungseigenschaft DisplayStatusBar auf True.
'Auswahl in Statusleiste anzeigen
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Application
.DisplayStatusBar = True
.StatusBar = "Auswahl: " & Target.Parent.Name & "!" & Target.AddressLocal
End With
End Sub
Da wir uns im Klassenmodul eines Tabellenblatts befinden, können Sie das Tabellenblatt selbst über die Anweisung Me ansprechen. Somit ersparen Sie sich den Umweg über Target.Parent.
'Auswahl in Statusleiste anzeigen
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Application
.DisplayStatusBar = True
.StatusBar = "Auswahl: " & Me.Name & "!" & Target.AddressLocal
End With
End Sub
Obiges Beispiel gilt nur für jenes Tabellenblatt, in dessen Klassenmodul die Prozedur implementiert ist. Beachten Sie, dass auch das Arbeitsmappenobjekt auf die Änderung der Auswahl in einem Tabellenblatt reagieren kann. Auf Arbeitsmappenebene wird das Ereignis SheetSelectionChange ausgelöst, das neben dem selektierten Bereich auch eine Referenz auf das Tabellenblatt, in dem die Auswahländerung erfolgt ist, entgegennimmt.
Das folgende Beispiel ist im Klassenmodul der Arbeitsmappe (Workbook) implementiert und wird ausgeführt, sobald sich die Auswahl in einem beliebigen Blatt der Arbeitsmappe ändert.
'Auswahl in Statusleiste anzeigen
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
With Application
.DisplayStatusBar = True
.StatusBar = "Auswahl: " & Sh.Name & "!" & Target.AddressLocal
End With
End Sub
Wenn Sie auf die Änderung der Auswahl sowohl auf Tabellenblatt- als auch auf Arbeitsmappenebene reagieren, so löst zuvor das Tabellenblatt das SelectionChange-Ereignis und danach die Arbeitsmappe das SheetSelectionChange-Ereignis aus.
Ist im oberen Bereich des Klassenmoduls im linken Auswahlfeld Worksheet ausgewählt, enthält das rechte Auswahlfeld alle Ereignisse, die das Tabellenblatt auslösen kann. Nach Auswahl des gewünschten Ereignisses wird die entsprechende ereignisbehandelnde Prozedur automatisch erstellt.
Um auf Änderungen von Zellinhalten zu reagieren, stellt das Tabellenblatt das Change-Ereignis zur Verfügung. Um zu überprüfen, ob sich der geänderte Bereich mit einem bestimmten Bereich im Tabellenblatt überschneidet, können Sie die Applikationsmethode Intersect verwenden. Liegt eine Überschneidung vor, so gibt die Methode Intersect den rechteckigen Bereich der Überschneidung zurück, sonst den Wert Nothing.
Im folgenden Beispiel kann der User durch Eingabe von 1, 2 oder 3 in der Zelle A1 die Schriftgröße aller Zellen des Tabellenblatts verändern.
'Abfangen von Änderungen in der Zelle A1
Private Sub Worksheet_Change(ByVal Target As Range)
Dim schriftGroesse As Integer
If Not Intersect(Target, Range("A1")) Is Nothing Then
Select Case Range("A1")
Case 1
schriftGroesse = 12
Case 2
schriftGroesse = 14
Case 3
schriftGroesse = 16
End Select
If schriftGroesse <> 0 Then
Me.Cells.Font.size = 12
End If
End If
End Sub
Da die obige Prozedur im Klassenmodul eines Tabellenblatts implementiert ist, bezieht sich die Anweisung Me auf das Tabellenblattobjekt selbst.
Sie wollen verhindern, dass der Benutzer durch Doppelklick in den Bearbeitungsmodus einer Zelle gelangt.
Editiermodus durch Doppelklick verhindern
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
End Sub
Durch Setzen der Variable Cancel auf True wird dem Tabellenblatt mitgeteilt, dass die Doppelklick-Aktion des Users abgebrochen werden soll.
Im folgenden Beispiel wird bei Aktivierung des Tabellenblattes die Zelle A1 ausgewählt.
Auswahl von A1 bei Aktivierung des Tabellenblatts
Private Sub Worksheet_Activate()
Me.Cells(1, 1).Select
End Sub
Weitere Ereignisse finden Sie im rechten Auswahlfeld im oberen Bereich des Klassenmoduls.