Ausführen von VBA-Code beim Auftreten von Ereignissen in Excel 2010

Zusammenfassung:  Informationen zum Ausführen von VBA-Code in Microsoft Excel 2010 (Visual Basic for Applications), wenn Ereignisse in einer Arbeitsmappe eintreten, z. B. wenn Benutzer die Auswahl ändern. Durch das Ausführen von VBA-Code bei Ereignissen können Sie Aufgaben automatisch ausführen lassen, ohne dass Benutzer auf Verknüpfungen klicken oder Makros ausführen müssen.

Letzte Änderung: Sonntag, 5. Juni 2011

Gilt für: Excel 2010 | Office 2010 | SharePoint Server 2010 | VBA

Inhalt dieses Artikels
Grundlegendes zu Ereignishandlern in VBA
Hervorheben der aktuellen Zeile und Spalte
Unterteilen in Bereiche an der aktuelle Zeile und Spalte
Hinzufügen eines Kommentars beim Ändern eines Zellwerts
Erstellen eines benutzerdefinierten Kontextmenüs
Starten eines Zeitgebers über eine Tastenkombination
Durchlaufen von Arbeitsblättern mit der BILD-NACH-OBEN- und BILD-NACH-UNTEN-TASTE
Automatisches Speichern von Änderungen an einer Arbeitsmappe mithilfe von Ereignissen
Schlussbemerkung
Weitere Ressourcen

Veröffentlicht:  Juni 2011

Beitrag von:   Dr. Gerard M. Verschuuren, Mr. Excel (in englischer Sprache) | Mark Roberts, Microsoft Corporation

Inhalt

• Grundlegendes zu Ereignishandlern in VBA

• Hervorheben der aktuellen Zeile und Spalte

• Unterteilen in Bereiche an der aktuelle Zeile und Spalte

• Hinzufügen eines Kommentars beim Ändern eines Zellwerts

• Erstellen eines benutzerdefinierten Kontextmenüs

• Starten eines Zeitgebers über eine Tastenkombination

• Durchlaufen von Arbeitsblättern mit der BILD-NACH-OBEN- und BILD-NACH-UNTEN-TASTE

• Automatisches Speichern von Änderungen an einer Arbeitsmappe mithilfe von Ereignissen

• Schlussbemerkung

• Weitere Ressourcen

  Beispielarbeitsmappe herunterladen: Ausführen von VBA-Code, wenn Ereignisse eintreten (Events.xlsm) (in englischer Sprache)

Grundlegendes zu Ereignishandlern in VBA

In diesem Artikel wird erläutert, wie Sie Aufgaben durch das Ausführen von VBA-Code (Visual Basic for Applications) erledigen können, wenn eine Aktion in einer Arbeitsmappe erfolgt, z. B. Öffnen der Arbeitsmappe, Erstellen oder Ändern der Auswahl in einer Arbeitsmappe, Klicken mit der rechten Maustaste oder Wechseln zwischen Arbeitsmappen. Die Aktionen oder Geschehnisse, die Sie VBA-Code zuordnen können, heißen Ereignisse. Die besondere Prozedur, die ausgeführt wird, wenn das Ereignis eintritt, wird als Ereignishandler bezeichnet.

Der Schwerpunkt der Beispiele in diesem Artikel liegt auf den Objekten Worksheet und Workbook sowie auf den beiden Methoden des Application-Objekts (OnKey und OnTime), die wie Ereignisse funktionieren. Listen der Ereignisse, die Microsoft Excel-Objekten zugeordnet sind, finden Sie unter Worksheet Object Events im Abschnitt Events, Worksheet Functions, and Shapes der Excel 2010-Referenz für Entwickler.

Zum Ausführen des in diesem Artikel beschriebenen Codes mit Beispieldaten laden Sie die Beispielarbeitsmappe Events (in englischer Sprache) herunter.

Hervorheben der aktuellen Zeile und Spalte

Die Beispielarbeitsmappe Events enthält eine Ereignishandlerprozedur, die dem Arbeitsblatt 1-Cross zugeordnet ist, und automatisch eine horizontale und vertikale Markierung anzeigt, die der Auswahl des Benutzers auf dem Blatt folgt (siehe Abbildung 1).

Abbildung 1. Hervorheben der aktuellen Zeile und Spalte

Zum Erstellen dieses Beispiels wird der Ereignishandlercode dem SelectionChange-Ereignis des Worksheet-Objekts zugeordnet. Wie der Name schon sagt, tritt das SelectionChange-Ereignis ein, wenn ein Benutzer seine Auswahl auf dem Arbeitsblatt ändert. Verwenden Sie die folgende Prozedur, um einen Ereignishandler für ein Arbeitsblatt hinzuzufügen.

So fügen Sie einen Ereignishandler für ein Arbeitsblattereignis hinzu

1. Klicken Sie auf der Registerkarte Entwicklertools auf Visual Basic.

2. Klicken Sie im Projektexplorer auf das Arbeitsblatt, mit dem Sie arbeiten möchten.

3. Wählen Sie in der linken Dropdownliste über dem Code-Editor Arbeitsblatt aus.

4. Wählen Sie in der rechten Dropdownliste über dem Code-Editor das Ereignis aus, für das Sie einen Ereignishandler erstellen möchten.

   Für das folgende Beispiel ist dies das SelectionChange-Ereignis, die Standardauswahl in der Dropdownliste.

Beachten Sie, dass der Ereignishandler für das SelectionChange-Ereignis ein Ereignisargument mit dem Namen Target aufweist, das Informationen zum Zellbereich bereitstellt, den ein Benutzer ausgewählt hat. Da jedoch das vom Target-Argument zurückgegebene Range-Objekt aus mehreren Zellen bestehen kann, verwendet der Code für diesen Ereignishandler stattdessen die ActiveCell-Eigenschaft des Application-Objekts. Die ActiveCell-Eigenschaft gibt ein Range-Objekt zurück, das nur auf eine Zelle verweist (die aktuelle Zelle oder Zelle links oben in einem Zellenbereich), mit deren Hilfe der Ereignishandler die Zeile und Spalte hervorhebt, in der sich der Benutzer befindet.

Der Code legt die ColorIndex-Eigenschaft des Inneren der Zeile und Spalte mit der ausgewählten Zelle auf Grau fest. (Im Thema zur ColorIndex-Eigenschaft finden Sie eine Liste der standardmäßigen Farbindexwerte.) Zuvor verwendet der Code die Cells-Eigenschaft des Worksheet-Objekts zum Löschen der inneren Farbe aller Zellen im Arbeitsblatt.

Der folgende Codeauszug enthält den Code für den SelectionChange-Ereignishandler im Arbeitsblatt 1-Cross.

    ' Highlight the entire row and column for the ActiveCell.
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Cells.Interior.ColorIndex = xlColorIndexNone
        ActiveCell.EntireRow.Interior.ColorIndex = 15
        ActiveCell.EntireColumn.Interior.ColorIndex = 15
    End Sub

Unterteilen in Bereiche an der aktuelle Zeile und Spalte

Das Arbeitsblatt 2-MoveButton in der Arbeitsmappe Events enthält eine Befehlsschaltfläche, die Ihrer Auswahl folgt. Durch Klicken auf die Schaltfläche wird der geteilte Bildschirm an dieser Stelle umgeschaltet.

Fügen Sie zum Erstellen dieses Features zunächst dem Arbeitsblatt eine Schaltfläche hinzu, indem Sie auf der Registerkarte Entwicklertools auf Einfügen und anschließend in der Palette ActiveX-Steuerelemente auf Befehlsschaltfläche klicken. Klicken Sie nach dem Zeichnen der Schaltfläche auf das Arbeitsblatt mit der rechten Maustaste, und klicken Sie dann auf Code anzeigen. Über diese Aktion wird der Code-Editor geöffnet und ein Click-Ereignishandler für die Schaltfläche erstellt.

Wenn (wie im folgenden Codeauszug gezeigt) ein Benutzer auf die Schaltfläche klickt, verwendet der Ereignishandlercode für die ToggleSplit-Schaltfläche eine If/Else-Anweisung, um den Wert der Split-Eigenschaft zu überprüfen, und aktiviert oder deaktiviert die Teilung abhängig vom aktuellen Wert. Darüber hinaus ändert der Code die Caption-Eigenschaft der Schaltfläche, um die Aktion wiederzugeben, die beim nächsten Klicken auf die Schaltfläche erfolgt.

    ' The button turns the window split on/off.
    Private Sub ToggleSplit_Click()
        If ActiveWindow.Split = True Then
            ActiveWindow.Split = False
            ToggleSplit.Caption = "Turn Split ON"
        Else
            ActiveWindow.Split = True
            ToggleSplit.Caption = "Turn Split OFF"
        End If
    End Sub

Damit die Schaltfläche der Benutzerauswahl folgt, ist ein Ereignishandler für die SelectionChange-Eigenschaft erforderlich, der dem Arbeitsblatt 2-MoveButton zugeordnet ist. Wie im folgenden Codeauszug gezeigt, legt der Code die Koordinaten Top und Left der Schaltfläche basierend auf den Werten der Koordinaten Top und Left der aktuelle Zelle fest, versetzt durch die Werte der Eigenschaften Height und Width dieser Zelle.

    ' Make the button follow the user's navigation.
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        ToggleSplit.Top = ActiveCell.Top + ActiveCell.Height
        ToggleSplit.Left = ActiveCell.Left + ActiveCell.Width
    End Sub

Hinzufügen eines Kommentars beim Ändern eines Zellwerts

Der Ereignishandlercode für das Arbeitsblatt 3-Comment verfolgt alle Änderungen nach, die der Benutzer an Zellen auf dem Blatt vornimmt, und fügt diese einem Kommentar hinzu, der nach jeder neuen Änderung erweitert wird. Das Erstellen dieses Features erfordert Ereignishandler für zwei neue Arbeitsblattereignisse:

• Das SelectionChange-Ereignis zum Aufzeichnen des aktuellen Werts der neu ausgewählten Zelle

• Das Change-Ereignis zum Erstellen eines Kommentars oder Hinzufügen einer neuen Zeile zu einem vorhandenen Kommentar, wenn Änderungen erfolgt sind

In den folgenden Ereignishandlern muss für die Variable OldValue ein Bereich relativ zum gesamten Codemodul des Arbeitsblatts festgelegt werden, damit ihr Wert im ersten Ereignishandler festgelegt und dieser Wert dann im zweiten Ereignishandler verwendet werden kann. Deklarieren Sie hierzu die Variable oben im Arbeitsblattmodul über den Prozeduren.

Der folgende Auszug zeigt den Code für die Prozedur des SelectionChange-Ereignishandlers, die den Wert der Variablen OldCellValue auf den aktuellen Wert festlegt, wenn die Zelle ausgewählt wird.

    ' Record the current cell value.
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        OldCellValue = ActiveCell.Text
    End Sub

Der Change-Ereignishandler verwendet dieses Mal das Argument Target, da ActiveCell nicht immer die erste Zelle einer Auswahl ist. Zunächst wird die folgende Codezeile verwendet, um sicherzustellen, dass das Argument Target nur auf eine Zelle verweist. Falls ja, wird die Prozedur beendet, da andernfalls wesentlich mehr Code geschrieben werden müsste.

    If Target.Cells.Count > 1 Then Exit Sub

Mit der nächsten Codezeile wird das Änderungsverlaufselement erstellt, das dem Kommentar hinzugefügt wird, und die Uhrzeit, den Namen des Benutzers, der die Änderung durchgeführt hat, und den vorherigen Wert enthält.

    NewComment = "Changed on " & Now() & " by " & Application.UserName & _
            " from " & OldCellValue

Die nächsten Codezeilen bestimmen, ob ein Kommentar der Zelle zugeordnet ist. Falls nicht, erstellt der Code einen neuen Kommentar und fügt anschließend das neu erstellte Änderungsverlaufselement hinzu. Andernfalls werden der Text des aktuellen Kommentars in der Variablen OldComment gespeichert, das neue Verlaufselement hinzugefügt und die alten Verlaufselemente angefügt.

    If Target.Comment Is Nothing Then
        Target.AddComment NewComment
    Else
        OldComment = Target.Comment.Text
        Target.Comment.Text NewComment & vbLf & OldComment
    End If

Mit der nächsten Codezeile wird das Kommentarfeld auf die breiteste Zeile festgelegt, indem die AutoSize-Eigenschaft auf True festgelegt wird. In den nachfolgenden Zeilen wird der Kommentar fünf Sekunden angezeigt. Da die Wait-Methode Excel an anderen Aktivitäten hindert, bis der Wartezeitraum abgelaufen ist, wird die DoEvents-Funktion aufgerufen, damit das Betriebssystem andere Aktionen verarbeiten kann.

    Target.Comment.Shape.TextFrame.AutoSize = True
    
    Target.Comment.Visible = True
    DoEvents
    Application.Wait Now + TimeValue("00:00:05")
    Target.Comment.Visible = False

In den letzten Codezeilen wird der Benutzer gefragt, ob die Änderungsverlaufskommentare gelöscht werden sollen. Falls ja, werden alle Zellen im aktuellen Bereich in einer Schleife durchlaufen, um Kommentare zu löschen.

    If MsgBox("Delete all comments?", vbYesNo) = vbYes Then
        For Each objCell In ActiveCell.CurrentRegion.Cells
            If Not objCell.Comment Is Nothing Then objCell.Comment.Delete
        Next objCell
    End If

Der folgende Codeauszug enthält den Code für die gesamte Prozedur des Change-Ereignishandlers, der dem Arbeitsblatt 3-Comment zugeordnet ist.

    ' Add a comment for each change.
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim OldComment As String, NewComment As String, objCell As Range
        
        If Target.Cells.Count > 1 Then Exit Sub
        
        NewComment = "Changed on " & Now() & " by " & Application.UserName & _
            " from " & OldCellValue
        
        If Target.Comment Is Nothing Then
            Target.AddComment NewComment
        Else
            OldComment = Target.Comment.Text
            Target.Comment.Text NewComment & vbLf & OldComment
        End If
        
        Target.Comment.Shape.TextFrame.AutoSize = True
        
        Target.Comment.Visible = True
        DoEvents
        Application.Wait Now + TimeValue("00:00:05")
        Target.Comment.Visible = False
        
        If MsgBox("Delete all comments?", vbYesNo) = vbYes Then
            For Each objCell In ActiveCell.CurrentRegion.Cells
                If Not objCell.Comment Is Nothing Then objCell.Comment.Delete
            Next objCell
        End If
    End Sub

Erstellen eines benutzerdefinierten Kontextmenüs

Das zum Arbeitsblatt 5-PopupMenu der Arbeitsmappe Events gehörige Beispiel veranschaulicht das Erstellen eines Kontextmenüs mit drei Befehlen. Der Code ist im BeforeRightClick-Ereignishandler des Arbeitsblatts enthalten. Außerdem werden drei Funktionen aus einem Codemodul aufgerufen, um die Befehle im Kontextmenü zu definieren.

Mit den ersten beiden Codezeilen werden Arrays angelegt, die zum Erstellen der drei Befehle des Kontextmenüs dienen. Das varCaption-Array enthält die anzuzeigenden Beschriftungen. Das varAction-Array enthält die Namen der Sub-Prozeduren, die für jeden Befehl aufgerufen werden.

    varCaption = Array("Toggle Gridlines", "Toggle Formulas", "Print Preview")
    varAction = Array("Gridlines", "Formulas", "Preview")

Die nächste Codezeile fügt ein neues CommandBar-Objekte hinzu, das als Kontextmenü für die CommandBars-Auflistung konfiguriert wird, und legt die Variable objMenu auf dieses Objekt fest.

    Set objMenu = CommandBars.Add(Position:=msoBarPopup, Temporary:=True)

Die folgenden Codezeilen durchlaufen die beiden Arrays varCaption und varAction, um die Befehle im Kontextmenü aufzufüllen.

    For i = 0 To UBound(varAction)
        Set objCommand = objMenu.Controls.Add
        objCommand.Caption = varCaption(i)
        objCommand.OnAction = varAction(i)
    Next i

Die letzten beiden Codezeilen zeigen das Kontextmenü an und legen anschließend das Argument des Cancel-Ereignisses des BeforeRightClick-Ereignishandlers auf True fest, um Excel daran zu hindern, sein vordefiniertes Kontextmenü einzublenden.

    objMenu.ShowPopup
    Cancel = True

Der folgende Auszug enthält den Code für die drei Prozeduren in Module1, die über das Kontextmenü aufgerufen werden, das in der Prozedur des BeforeRightClick-Ereignishandlers definiert ist. Die Prozeduren Gridlines und Formulas schalten die Eigenschaften DisplayGridlines und DisplayFormulas um, und die Preview-Prozedur zeigt das Arbeitsblatt in der Seitenansicht an.

    Sub Gridlines()
        ActiveWindow.DisplayGridlines = Not ActiveWindow.DisplayGridlines
    End Sub
    
    Sub Formulas()
        ActiveWindow.DisplayFormulas = Not ActiveWindow.DisplayFormulas
    End Sub
    
    Sub Preview()
        ActiveSheet.PrintPreview
    End Sub

Der folgende Code enthält den gesamten Auszug für die Prozedur des BeforeRightClick-Ereignishandlers, der dem Arbeitsblatt 5-PopupMenu zugeordnet ist.

    Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
        Dim varCaption As Variant, varAction As Variant, i As Integer
        Dim objMenu As CommandBar, objCommand As CommandBarControl
        
        ' Create array of shortcut menu captions.
        varCaption = Array("Toggle Gridlines", "Toggle Formulas", "Print Preview")
        
        ' Create array of the Sub procedure names to call from shortcut menu.
        varAction = Array("Gridlines", "Formulas", "Preview")
        
        ' Add shortcut menu to CommandBars collection.
        Set objMenu = CommandBars.Add(Position:=msoBarPopup, Temporary:=True)
        
        ' Loop through arrays to add commands to shortcut menu.
        For i = 0 To UBound(varAction)
            Set objCommand = objMenu.Controls.Add
            objCommand.Caption = varCaption(i)
            objCommand.OnAction = varAction(i)
        Next i
        
        ' Display shortcut menu.
        objMenu.ShowPopup
        
        ' Cancel display of the built-in shortcut menu.
        Cancel = True
    End Sub

Starten eines Zeitgebers über eine Tastenkombination

Das Beispiel in diesem Abschnitt zeigt, wie Sie die OnKey-Methode des Application-Objekts verwenden, die wie ein Ereignis funktioniert, um eine Prozedur auszuführen, wenn eine Taste oder Tastenkombination gedrückt wird. Die von der OnKey-Methode in diesem Beispiel aufgerufene Prozedur verwendet die OnTime-Methode des Application-Objekts, um nach einem festgelegten Zeitraum einen Alarm auszulösen.

Der Code, der die zu drückende Taste und aufzurufende Prozedur bestimmt, ist eine einzelne Zeile im Open-Ereignishandler für die Arbeitsmappe. Der Aufruf der OnKey-Methode im folgenden Codeauszug gibt an, dass beim Drücken der ENDE-TASTE die SetAlarm-Prozedur aufgerufen wird, die das Alarmverhalten definiert.

    Private Sub Workbook_Open()
        Application.OnKey Key:="{END}", Procedure:="SetAlarm"
    End Sub

Der folgende Auszug zeigt die SetAlarm-Prozedur in Module1, die aufgerufen wird, wenn der Benutzer die ENDE-TASTE drückt. Die Prozedur fordert den Benutzer auf, die Einstellung für den Alarm anzugeben. Sie ruft anschließend die OnTime-Methode mit dem vom Benutzer angegebenen Wert auf, um zu bestimmen, wann die ShowTime-Prozedur zum Anzeigen der Zeit aufgerufen werden soll.

    Sub SetAlarm()
        Dim strAlarm As String
        strAlarm = InputBox(Prompt:="At what time? (24-hour clock)", Title:="Set Alarm")
        If strAlarm = "" Then Exit Sub
        Application.OnTime EarliestTime:=TimeValue(strAlarm), Procedure:="ShowTime"
    End Sub

Wie im folgenden Auszug gezeigt, zeigt der Code für die ShowTime-Prozedur die aktuelle Zeit und ruft anschließend die SetAlarm-Prozedur erneut auf, bis der Benutzer die Eingabeaufforderung InputBox abbricht.

    Sub ShowTime()
        MsgBox Now
        SetAlarm
    End Sub

Durchlaufen von Arbeitsblättern mit der BILD-NACH-OBEN- und BILD-NACH-UNTEN-TASTE

Im Beispiel in diesem Abschnitt wird die OnKey-Methode verwendet, um sich mit der BILD-NACH-OBEN- und BILD-NACH-UNTEN-TASTE durch eine Arbeitsmappe bewegen zu können.

Wie im vorherigen Beispiel befinden sich die Aufrufe der OnKey-Methode im Open-Ereignishandler der Arbeitsmappe (siehe den folgenden Code).

    Private Sub Workbook_Open()
        Application.OnKey Key:="{PgUp}", Procedure:="SheetsUp"
        Application.OnKey Key:="{PgDn}", Procedure:="SheetsDown"
    End Sub

Der folgende Code enthält den Auszug für die Funktionen SheetsUp und SheetsDown in Module1, die über die Aufrufe der OnKey-Methode in der Prozedur des Open-Ereignishandlers der Arbeitsmappe aufgerufen werden. Der Code für die SheetsUp-Prozedur erhöht den Wert des Indexes des aktiven Arbeitsblatts um 1 und prüft anschließend, ob dieser Wert die Anzahl der Arbeitsblätter übersteigt. Falls nicht, wechselt die Prozedur zum nächsten Arbeitsblatt. Beim Code für die SheetsDown-Prozedur ist es umgekehrt.

    Sub SheetsUp()
        Dim i As Long
        i = ActiveSheet.Index + 1
        If i <= Sheets.Count Then Sheets(i).Select
    End Sub
    
    Sub SheetsDown()
        Dim i As Long
        i = ActiveSheet.Index - 1
        If i >= 1 Then Sheets(i).Select
    End Sub

Automatisches Speichern von Änderungen an einer Arbeitsmappe mithilfe von Ereignissen

Um die Arbeitsmappe nach einem angegebenen Intervall mithilfe von Code zu speichern, können Sie einen Aufruf der OnTime-Methode des Application-Objekts in der Prozedur des Open-Ereignishandlers der Arbeitsmappe verwenden.

Die Codezeile in der Ereignishandlerprozedur des Open-Ereignisses der Beispielarbeitsmappe Events ruft die Prozedur SaveWB 10 Minuten nach Öffnen der Arbeitsmappe auf.

    Private Sub Workbook_Open()
        Application.OnTime EarliestTime:=Now + TimeValue("00:10:00"), Procedure:="SaveWB"
    End Sub

Wie im folgenden Code gezeigt, zeigt die erste Codezeile in der SaveWB-Prozedur dem Benutzer eine Eingabeaufforderung an. Wenn der Benutzer bejaht, ruft die Prozedur die Save-Methode auf. Die zweite Codezeile wiederholt diesen Aufruf der OnTime-Methode als Prozedur des Open-Ereignishandlers, um einen rekursiven Vorgang zu erstellen, der den Zeitgeber neu startet.

    Sub SaveWB()
        If MsgBox("Save workbook?", vbYesNo) = vbYes Then ActiveWorkbook.Save
        Application.OnTime EarliestTime:=Now + TimeValue("00:10:00"), Procedure:="SaveWB"
    End Sub

Wie im folgenden Code gezeigt, können Sie auch den Benutzer fragen, ob die Arbeitsmappe gespeichert werden soll, wenn er zu einem anderen Arbeitsblatt wechselt, indem eine Prozedur wie SaveWB in einer Ereignishandlerprozedur für das SheetDeactivate-Ereignis aufgerufen wird.

    Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
        SaveWB
    End Sub

Schlussbemerkung

Dieser Artikel und die Beispielarbeitsmappe Events bieten Beispiele für die Ausführung von VBA-Code, wenn Ereignisse in einer Arbeitsmappe eintreten, z. B. wenn ein Benutzer die Auswahl oder den Wert in einer Zelle ändert.

Weitere Ressourcen

• Worksheet Object Events

• Erste Schritte mit VBA in Excel 2010

• Excel Developer Center

• Office Developer Center