Workbook_SheetChange

[GeheimnisExcel]

Hallo Forum.
ich habe die Routine "Workbook_SheetChange" im Einsatz, um Änderungen innerhalb einer Tabelle zu überprüfen.

Über die Routine "Workbook_SheetBeforeDoubleClick" habe ich es eingerichtet, dass eine bestimmte Zeile kopiert und am Ende der Liste eingefügt werden kann. Meine Befehle dazu:

Rows(ActiveCell.Row).Copy
Rows(InsertRow).Insert

Mein Problem und damit die Frage: warum wird die Routine "Workbook_SheetChange" zweimal aufgerufen. Änderungen werden in dieser Routine aktuell nicht durchgeführt. Direkt nach "Exit Sub" in dieser Routine und Ausführung des nächsten Befehls mit F8 wird diese Routine erneut aufgerufen. Warum?

 

[Gameforce]

Weil derr zweite Befehle eine Änderung der Tabelle durchführt.
Deshalb ruft wahrscheinlich der zweite Befehl diese "Workbook_SheetChange" an sich nochmal auf.

 

[GeheimnisExcel]

Das verstehe ich nicht. Klar: eine Zeile wird eingefügt, und das ist eine Änderung. Und dann????

 

[schalli110]

https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.sheetchange

Workbook_SheetChange zeigt dir die Range an, die geändert wurde.
Hast du mal geschaut, was da drin steht?

 

[GeheimnisExcel]

Es ist bei beiden Aufrufen dieselbe Target.Row. Weitere Parameter innerhalb Target habe ich nicht überprüft. Der Link zu MS bringt mich auch nicht weiter.

 

[RPP63]

Du solltest mal den gesamten Code einstellen.
Verwende dazu Code-Tags:

Warum verwendest Du ActiveCell und nicht das hier vorgesehene Target?
In der Regel muss man bei Ereignismakros EnableEvents erst aus- und am Ende einschalten.
Wichtig dabei, dies in eine vernünftige Fehlerroutine einzubetten.
Application.EnableEvents = False ist nämlich nicht flüchtig!

 

[GeheimnisExcel]

Ich verstehe nicht, was es bringen soll, wenn ich den gesamten Code bereitstelle. Dazu sind dann auch die Daten im Tabellenblatt erforderlich.

Auslöser für die genannten Events sind ausschließlich die zwei Befehle in meiner DoubleClick-Routine. Und das mit "Rows(ActiveCell.Row).Copy" habe ich aus einem Beispiel - ohne Nachzudenken - übernommen. Inzwischen habe ich es durch "Rows(Target.Row).Copy" ersetzt.

Wo sollte ich "Application.EnableEvents=False" setzen? Vor dem "Rows(InsertRow).Insert"? Dann wird aber auch die "Workbook_SheetChange"-Routine nicht mehr aufgerufen. Das kann nicht die Lösung sein. Und danach ist es zu spät.

Versuchsweise habe ich in Workbook_SheetChange "Application.EnableEvents" auf False gesetzt. Das hat geholfen; Workbook_SheetChange wird jetzt nicht ein weiteresmal aufgerufen. Doch wer sollte jetzt den Status wieder auf "True" setzen? Ohne "True" gibt es keinen weiteren Doppel-Klick!
Auch das kann keine Lösung sein, zumal die Workbook_SheetChange"-Routine durch Aktionen an unterschiedlichen Stellen im Code ausgelöst werden kann.

Mein (verkürzter) aktueller Code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Debug.Print "Workbook_SheetChange"
Debug.Print Cells(Target.Row, 2)
Application.EnableEvents = False 'Testweise
End Sub

Sinn und Zweck der Workbook_SheetChange-Routine ist es doch, die vorgenommene Änderung zu überprüfen. Und woher sollte ich nun wissen, dass genau dieses Prüfung für genau dieselbe Zeile Bruchteile einer Sekunde vorher schon einmal gemacht - und ggf. weitere Änderungen vorgenommen wurden?

 

[cumulonimbus8]

Deshalb ruft wahrscheinlich der zweite Befehl diese "Workbook_SheetChange" an sich nochmal auf.

Das genau ist für mich der VBA-Fluch!

Wenn in einem Aktionsmakro etwas passiert das ein anders Aktionsmakro auslösen würde (als wäre dieses Tun im echten Leben passiert) dann wird in der Tat jenes andere Aktionsmakro ausgelöst.

Saublöd ist, dass so ein Aktionsmakro auch sich selbst aufrufen kann; Rekursiv bis theoretisch endlos.
Application.EnableEvents = False
ist unbedingte Pflicht oberhalb jeder Aktion die das Makro auslösen würde. Nie probiert, aber auch das Lesen einer Zelle könnte in deinem Falle das erregende Moment sein…

Nur eben vor End Sub nicht Application.EnableEvents = True vergessen.

CN8

 

[GeheimnisExcel]

Warum ich Application.EnableEvents = False an der fraglichen Stelle nicht benutzen kann, habe ich doch ausführlich beschríeben.

Muss ich mit dem Problem "works as designed" leben??

 

[Darkman.X]

Das verstehe ich nicht. Klar: eine Zeile wird eingefügt, und das ist eine Änderung. Und dann????

Ich verstehe die Frage nicht. Wie "Und dann"? Das ist eine Änderung, also wird das Event erneut aufgerufen.

Muss ich mit dem Problem "works as designed" leben??

Ich verstehe dein Problem nicht.
Auf deinen 1. Post bezogen:

Rows(ActiveCell.Row).Copy
Application.EnableEvents = False
Rows(InsertRow).Insert
Application.EnableEvents = True

Das verhindert, dass das Event ein 2. Mal wegen dem Insert gestartet wird. Sonst musst du bitte genauer beschreiben, was dich daran stört.

 

[Gameforce]

Wenn Application.EnableEvents = True usw. nicht geht dann:


Du kannst auch eine globale Variable definieren. Z.B.

Global Working as Boolean

Und diese Variable prüfst Du am Anfang deiner Routine ab, ob diese auf "True" gesetzt ist, wenn "True" dann Abbruch der Routine. Wenn nicht, dann Variable auf True setzen und Code ausführen lassen und am Ende der Routine die Variable wieder auf False setzen. So verhinderst Du ein zweites Aufrufen des Ereignisses, wenn Deine Routine das Ereignis nochmals aufrufen sollte.

 

[GeheimnisExcel]

Wie ich weiter oben schon geschrieben habe, kann ich auf Application.EnableEvents nicht verzichten, weil sonst meine SheetChange-Routine nicht aufgerufen wird. Die wird allerdings nicht nur beim Einfügen oder Löschen von Zeilen aufgerufen, sondern auch dann, wenn innerhalb einer Zeile eine Änderung vorgenommen wird. Und danach wird in der SheetChange-Routine die geänderte bzw. eingefügte(n) Zeile(n) geprüft und mit in der Tabelle davor stehenden Daten abgeglichen.

Der Vorschlag von Gameforce scheitert auch daran, dass niemand die globale Variable setzt, bevor er in einer bestehenden Zeile eine Änderung durchführt.

Ich habe mich entschlossen, den Thread nicht mehr fortzuführen, und werde versuchen, den doppelten Aufruf der Sheet-Change-Routine im Zusammenhang mit "Insert" durch "Logik" zu erkennen.

Danke an alle, die mir geantwortet haben.

 

[cumulonimbus8]

Wie ich weiter oben schon geschrieben habe, kann ich auf Application.EnableEvents nicht verzichten, weil sonst meine SheetChange-Routine nicht aufgerufen wird.

Man muss diese Befehle halt zielgerichtet setzen und nicht irgendwo global!

Ich arbeite zu oft mit dem Aktionsmakro das auf Zelländerungen anspricht.
Und da muss ich die Events abschalten bevor ich den Inhalte jener Eingaben ggf. korrigiere. Und dann schalte ich das wieder an!
Innerhalb dieses Aktionsmakro, nur für dieses Aktionsmakro. Aber es mehrfach darin tun zu müssen hatte ich noch nie (nötig)…

CN8

 

[Darkman.X]

Leider möchte der TE das Thema nicht mehr fortsetzen.
Der Startpost war ja, dass er sich darüber wundert, dass das "Workbook_SheetChange"-Event zweifach ausgeführt wird. Es wurde der Grund und zwei Möglichkeiten genannt, wie man das Event temporär im 1. Durchgang deaktivieren kann. Aber das scheint ja auch nicht zu passen und ich habe den Grund bis jetzt nicht verstanden.

Ich frage mich schon, ob hier vielleicht ein XY-Problem vorliegt und der TE nach einer Lösung für einen verquerten Gedanken sucht. Und damit wären wir wieder bei der Anfrage nach dem Gesamtcode von @RPP63, um das Problem besser zu verstehen. Aus der Ferne ist es halt schwierig, nur mit Codefragmenten zu helfen.

Man könnte natürlich auch hinterfragen, warum man mit soetwas globalen wie dem "Workbook_SheetChange"-Event arbeiten muss. Reichen die normalen "Worksheet_Change"-Events nicht aus? Man könnte damit beispielsweise den Insert in einem anderen Blatt durchführen und darüber einen eigenen "Worksheet_Change"-Event ausführen lassen oder halt nicht.

 

[GeheimnisExcel]

Warum "Workbook_SheetChange"?

Dann brauche ich nur eine Routine für alle Tabellenblätter pflegen. In besagter Mappe sind Blätter für verschiedene Jahre. Wenn sich jetzt am Code etwas ändert, und ich den Worksheet_Change-Event nutze, muss ich in allen Tabellenblättern den Code anpassen.

Darum.

Und nochmals mein Problem: ich möchte die Änderung an einer Tabelle überprüfen, egal, ob das direkt mit einer Eingabe gemacht wird, oder durch eine Aktion wie dem Doppelklick. Deshalb kann ich den Event nicht abschalten. Tatsache ist (was ich durch schrittweise Verarbeitung nachvollziehen konnte) , dass die Workbook_SheetChange-Routine durch die "insert"-Aktion 2 mal aufgerufen wird. Nachdem die Workbook_SheetChange-Routine beendet wurde, wird sie gleich wieder aufgerufen, also nicht aus der Routine heraus. Da bringt es auch nichts, innerhalb der Routine den Event aus und später wieder einzuschalten. Und der zweite Aufruf kommt (vermutlich) nur in Verbindung mit "insert".

 

[Darkman.X]

Danke für deine Erklärung.

{Den ersten Textblock gelöscht, weil der mögliche Workaround Nachteile hatte}

Zu deiner zweiten Hälfte der Antwort:
Ich glaube, dass ich dein Problem langsam verstehe, aber ich kann es nicht nachvollziehen. Wenn ich einen Insert ausführe, dann wird das "Workbook_SheetChange"-Event sofort erneut gestartet, was dann (wie erwartet) in vielen verschachtelten, laufenden "Workbook_SheetChange"-Events endet, weil jedes Mal erneut ein Insert durchgeführt wird, was wiederum das Event sofort triggert.

Laut deiner Antwort wird aber das 1. "Workbook_SheetChange"-Event erst komplett abgearbeitet, bevor ein 2. "Workbook_SheetChange"-Event startet. Das ist in meinem (Laien-)Augen ein unnatürliches Verhalten, welches ich bei den diversen "...Change"-Events, die Excel kennt, bisher nie beobachten konnte (Excel 2013, 2019 und 365). Dann wäre die Existenz von Application.EnableEvents ja auch sinn-/nutzlos.

Hast du in deinem Dokument mal eine Haltemarke beim Insert-Befehl gesetzt und wenn der Code dort stoppt, dann mit F8 (Einzelschritt) geprüft, ob nach dem Insert das Change-Event wirklich erstmal komplett abgearbeitet wird?
Oder kannst du mit einem frisches Excel-Dokument und mit Minimal-Code das Verhalten nachstellen?

Ansonsten bräuchte es doch eine Beispiel-Datei, damit man es nachvollziehen kann.

 

[cumulonimbus8]

Ich stimme dem zu.
Je globaler das «Ereignis» (Workbook_SheetChange) ist desto öfter wird es aus seinem Code heraus wieder getriggert. Und das ist dann nicht globale Vereinfachung sondern Trick 17 mit Selbstüberlistung.
CN8

 

[GeheimnisExcel]

Eines möchte ich noch nachtragen: ich habe "mein" Problem inzwischen gelöst.

Da ich nicht unterstelle, dass ein Anwender innerhalb kürzester Zeit eine zweite Eingabe machen kann, habe ich im Exit Workbook_SheetChange eine globale Variable definiert:

Public WBSCTime As Double

Mit folgendem Code kann ich den "automatischen zweiten Call" erkennen:

If (Timer - WBSCTime) < 0.01 Then
'Debug.Print "secondcall"
GoTo SecondCall
End If

.....

SecondCall:
WBSCTime = Timer

End Sub

Ergänzung (22. Mai 2023)

Um das Problem nachstellen zu können, habe ich wunschgemäß eine Mustertabelle hochgeladen.

Im Tabellenblatt "Tabelle1" befinden sich die beiden Event-Routinen Worksheet_Change und Worksheet_BeforeDoubleClick. In beiden Routinen ist ein "Stop" eingebaut.

Im Tabellenblatt auf eine beliebige Zeile einen Doppelklick ausführen. Diese Zeile wird dann hinter die letzte belegte Zeile kopiert.

Ablauf:

"Worksheet_BeforeDoubleClick" wird aufgerufen
...
"Rows(Target.Row).Copy"
"Stop"
"Rows(LastRow + 1).Insert" wird ausgeführt
"Worksheet_Change" wird aufgerufen
"Stop"
Debug.Print wird ausgegeben
"End Sub"
"Worksheet_Change" wird erneut aufgerufen
"Stop"
Debug.Print wird ausgegeben
"End Sub"
zurück im "Worksheet_BeforeDoubleClick"-Event
Debug.Print "kopiert"
"Cancel = True"
"End Sub"

Ergänzung (22. Mai 2023)

Leider möchte der TE das Thema nicht mehr fortsetzen.
Der Startpost war ja, dass er sich darüber wundert, dass das "Workbook_SheetChange"-Event zweifach ausgeführt wird. Es wurde der Grund und zwei Möglichkeiten genannt, wie man das Event temporär im 1. Durchgang deaktivieren kann. Aber das scheint ja auch nicht zu passen und ich habe den Grund bis jetzt nicht verstanden.

Ich frage mich schon, ob hier vielleicht ein XY-Problem vorliegt und der TE nach einer Lösung für einen verquerten Gedanken sucht. Und damit wären wir wieder bei der Anfrage nach dem Gesamtcode von @RPP63, um das Problem besser zu verstehen. Aus der Ferne ist es halt schwierig, nur mit Codefragmenten zu helfen.

Man könnte natürlich auch hinterfragen, warum man mit soetwas globalen wie dem "Workbook_SheetChange"-Event arbeiten muss. Reichen die normalen "Worksheet_Change"-Events nicht aus? Man könnte damit beispielsweise den Insert in einem anderen Blatt durchführen und darüber einen eigenen "Worksheet_Change"-Event ausführen lassen oder halt nicht.

 

[Darkman.X]

Nochmals Danke, dass du dir die Mühe für uns (mich) gemacht hast.

Ich kann es nachstellen und habe keine Ahnung, warum es so ist. Ich hätte zwar eine Vermutung, aber ich weiß nicht ob das überhaupt Sinn macht.
Wenn eine Zelle den Schreibfokus hat (mit F2 oder Doppelklick initiiert) und man eine andere Zelle anklickt (=Fokus-Verlust für die vorherige Zelle), dann wird das Change-Event auch ausgelöst. Vielleicht führt das "Insert" einen temporären Fokus-Verlust durch und deshalb die zweifache Ausführung des Change-Events (1x durch das "Insert" selber und 1x der temporäre Fokus-Verlust).

Das ist aber nur wild geraten. Denn wenn ich nicht mit einem "Insert", sondern normal in eine Zelle schreibe (z.B. Cells(LastRow + 1, 1).Value = "Test" in deinem BeforeDoubleClick-Event), dann wird das Change-Event nur einmal ausgeführt.

Das ist wohl ein "Insert"-spezifische Verhalten in Kombination mit dem BeforeDoubleClick-Event und läuft daher doch darauf hinaus:

Muss ich mit dem Problem "works as designed" leben??

(meiner Meinung nach, bin aber auch nur ein überdurchschnittlicher Anfänger / schlechtes Mittelmaß)