Bei Änderung → VBA

Bei Änderung einer Zelle: Makro starten

Excel 2003 (und frühere Versionen), 2007 ff

Recht oft wird für eine Excel-Tabelle folgende Forderung aufgestellt:

  • “Immer, wenn sich der Inhalt einer bestimmten Zelle ändert, dann soll eine definierte Aktion ausgeführt werden.

Was ganz harmlos klingt, ist nicht immer so ohne weiteres mit “reinem” Excel zu bewerk­stel­li­gen. Dazu bedarf es in bestimmten Fällen der Programmierung, vorzugsweise mit VBA. An einem Beispiel möchte ich Ihnen verdeutlichen, was ohne Programmierung möglich ist und wo Sie dann doch noch den Code erstellen müssen.

▲ nach oben …

Ohne VBA möglich …

Von einer an den Rechner angeschlossenen Wetterstation wird automatisch alle 10 Minu­ten die Temperatur ausgegeben und an den Computer gesendet. Es ist nicht vorgesehen, in Excel eine lange Liste zu erstellen, wo die einzelnen Werte zeilenweise aufgeführt sind. Es soll nur aber stets aktuell in A1 die von dem Messfühler übergebene Temperatur angezeigt werden und in B1 soll der Text unter 20°C, genau 20°C oder über 20°C stehen.

Das lässt sich relativ problemlos lösen, indem Sie in B1 diese Formel schreiben:

=WENN(A1<20; “unter”; WENN(A1=20;”genau”; “über”)) & ” 20°C”

Hinweis: Das Grad-Zeichen ist das erste Zeichen der Tastatur unterhalb der Reihe mit den Funktions-Tasten, Shift^. Sie können auch die oben stehende Befehlszeile kopieren und dann bei Bedarf in Excel an entsprechender Stelle einfügen.

▲ nach oben …

Nur mit VBA machbar …

Wenn nun aber die zusätzliche Forderung kommt, dass in den Zellen B4:B6 die aktuelle (addierte) Anzahl der Werte steht, dann sieht das schon etwas anders aus. Noch einmal zur Verdeutlichung: Wenn in A1 ein neuer Wert eingetragen wird, dann soll nicht nur
B1 aktualisiert werden sondern automatisch auch die Werte in A4:B6. Hier ein Beispiel:

Beispiel, wie die Tabelle aufgebaut sein soll

Ansicht des Tabellenaufbaus

Angenommen, der nächste Wert in A1 wird 23° sein. Dann soll automatisch der Wert in Zelle B6 um den Wert 1 erhöht werden, weil die Temperatur ja über 20° lag. Die beiden anderen Ergebniszellen sollen selbstverständlich unverändert bleiben.

Hier ist ein Makro, also VBA gefragt. Ich stelle Ihnen an dieser Stelle einfach einmal einen denkbaren (meinen) Programmcode vor. Der eigentliche Lerneffekt ist hier nicht der durchaus einfach gestaltete Programmcode sondern

  1. Der Name der Prozedur (Sub) selbst sowie der Aufbau des Prozedurnamens und
  2. Wie Sie solch einen Code in ein Arbeitsblatt einbinden.

Hier die Programmzeilen:

Option Explicit

Private Sub WorkSheet_Change(ByVal Target As Range)
   Dim Grad As Single
   
   If Target = Range("A1") Then
      Grad = Target.Value
      If Grad < 20 Then
         Range("B4").Value = Range("B4").Value + 1
      ElseIf Grad = 20 Then
         Range("B5").Value = Range("B5").Value + 1
      Else
         Range("B6").Value = Range("B6").Value + 1
      End If
   End If
End Sub

Diesen nicht unbedingt schönen aber funktionellen Code müssen Sie nun noch in die entsprechende Arbeitsmappe integrieren. Der Code soll nicht in der gesamten Mappe sondern nur in der einen Tabelle ausgeführt werden, wo auch die Werte hinein geschrie­ben werden. Sie müssen nun darauf achten, dass die richtige Tabelle ausgewählt wird. Dazu gehen Sie bei geöffneter Arbeitsmappe so vor:

  • AltF11, damit gelangen Sie in den VBA-Editor.
  • Führen Sie in diesem Fenster nun links oben einen Doppelklick auf die entsprechende Tabelle aus, hier ist die Position bei Tabelle1 entsprechend markiert:
Das Fenster des VBA-Editors ohne Schreibbereich

Das “jungfräuliche” Fenster des VBA-Editors ohne Schreibbereich

Sofort wird sich im rechten Teil des Fensters der Hintergrund auf weiß ändern und ein Schreibcursor zeigt an, dass in den Editor Daten eingegeben werden können:

Das Fenster des VBA-Editors ohne Schreibbereich

Und jetzt mit der Möglichkeit, Code zu schreiben

  • Wenn Sie dieses Beispiel nachvollziehen wollen, dann kopieren Sie einfach den weiter oben gezeigten Code in den Bereich hinein. Das wird dann (mit Ausnahme der Fenstergröße) etwa so aussehen:
Hier ist der Programmcode eingefügt

Hier ist der Programmcode eingefügt

  • Wahrscheinlich haben Sie reichlich überflüssige Leerzeilen nach dem Einfügen. Die können Sie gerne löschen, das erhöht die Übersichtlichkeit.
  • Schließen Sie das Fenster des Editors und probieren
    aus, ob alles wie gewollt und vorgesehen läuft.

▲ nach oben …

Ob Sie nun vorgegebenen Code (abgetippt oder per copy and paste) oder Eigenkreationen per Hand eingeben, das bleibt sich gleich. Das Vorgehen ist immer das gleiche. Es bleiben noch zwei Fragen: Warum läuft das alles wie gewünscht bei Änderungen in der Tabelle und wie kann ich mir das Leben bei selbst erstelltem Code erleichtern?

Der erste Teil der Frage ist ganz einfach zu beantworten: Der Name der Prozedur, das

Sub WorkSheet_Change(ByVal Target As Range)

bewirkt, dass diese komplette Prozedur (die Sub) von Anfang bis Ende ausgeführt wird, wenn sich irgendetwas im Arbeitsblatt (WorkSheet) ändert (change). Welche Zelle gerade geändert worden ist, das steht in Target drinnen. Im restlichen Code wird dann abgefragt, ob es die Zelle, wo Änderungen vorgenommen worden sind, A1 ist. Und wenn dieses der zutrifft, wird die dort geänderte bzw. neu geschriebene Zahl ausgewertet.

Wenn Sie selber den Code schreiben wollen und nicht ganz genau die Syntax der Namenszeile kennen, dann gehen Sie so vor:

  • Im Editorfenster Doppelklick auf die gewünschte Arbeitsmappe.
  • Wählen Sie nun an der markierten Stelle den Eintrag WorkSheet aus:
Erster Schritt zur Auswahl des Prozedurnamens

Erster Schritt zur Auswahl bzw. Erstellung des Prozedurnamens

Umgehend wird eine leere Prozedur erstellt. Diese ist für uns nicht relevant, weil der Name nicht stimmt. Darum …

  • Im nächsten Schritt wählen Sie im rechten DropDown, bei den Deklarationen das Ereignis Change aus:
Auswahl des Change - Ereignisses

Auswahl des Change – Ereignisses

  • Löschen Sie nun noch die überflüssige, zu Beginn automatisch eingefügte leere Prozedur WorkSheet_SelectionChange und füllen Sie die verbliebene Sub mit Ihrem Code.
  • Vergessen Sie nicht das Speichern (StrgS), bevor Sie Ihren Code testen! Selbst Profis machen das so, denn es schleicht sich zu schnell doch einmal ein Fehler ein, der das Programm zum “aufhängen” bringt oder einfach einen Absturz herbeiführt. In solchen Fällen ist der Code “gerettet” und kann nach einem erneuten Aufruf des Editors bearbeitet werden.

Wenn Sie Excel 2007 oder neuer verwenden, dann ist es denkbar, dass das Makro nicht ausgeführt wird. Das hängt dann wahrscheinlich mit den Sicherheitseinstellungen zusammen. Nähere Hinweise und Hilfe erhalten Sie hier.

[NachObenLetzte Verweis=”ML: Bei Änderung-Makro”]
Dieser Beitrag wurde unter Mit VBA/Makro, Musterlösungen abgelegt und mit , , , , , , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.