Datenchaos wegen Target.Calculate

Forumthread: Datenchaos wegen Target.Calculate

20.11.2025 16:10:26

Kolja

Hallo zusammen,

ich habe Probleme mit der Anwendung von Target.Calculate (für bedingte Formatierung zur Zeilenfärbung).

Folgende zwei Beispieldateien seien gegeben:

Ich habe zwei XSLM-Dateien erstellt (Test-01.xslm und Test-02.xslm).

Datei Test-01.xslm beinhaltet:
- Blatt 1 namens "Übersicht" eine Übersichtstabelle
- weitere Blätter 2 bis 6 namens "Probe 01" bis "Probe 05"
---> auf den Blättern Nr. 2 bis 6 ist jeweils in Zelle A1 der Wert "Probewert 0001" bis "Probewert 0002" eingetragen.
---> auf Blatt 1 gibt es eine Übersichtstabelle im Bereich B6:D23 mit 3 Spalten: Blatt-Nummer, Blatt-Name und Zellwert-A1 :
Spalte 1 gibt Blattnummern vor, Spalte 2 ermittelt aus betreffendem Blatt den Blattnamen, Spalte 3 holt aus betreffendem Blatt den Wert aus Zelle A1

Datei Test-02.xslm beinhaltet:
- Blatt 1 namens "Übersicht" eine Übersichtstabelle
- weitere Blätter 2 bis 13 namens "AAAAA" bis "LLLLLL"
---> auf den Blättern Nr. 2 bis 13 ist jeweils in Zelle A1 der Wert "aa" bis "ll" eingetragen.
---> auf Blatt 1 gibt es eine Übersichtstabelle im Bereich B6:D23 mit 3 Spalten: Blatt-Nummer, Blatt-Name und Zellwert-A1 :
Spalte 1 gibt Blattnummern vor, Spalte 2 ermittelt aus betreffendem Blatt den Blattnamen, Spalte 3 holt aus betreffendem Blatt den Wert aus Zelle A1

Des weiteren wurde in beiden Dateien jeweils der Tabellenbereich B6:C23 auf Blatt 1 mit einer bedingten Formatierung "Füllung"
mit folgender Regelformel =Zeile()=Zelle("Zeile") versehen.

In VBA wurde bei beiden Dateien jeweils für dieses Blatt 1 unter Worksheet folgender Code eingegeben:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Calculate
End Sub

--------------------------------------

Fehlergeschehen:

Sofern ich nur eine der beiden Tabellen öffne, funktioniert alles so, wie es soll:
- die korrekten Blattnamen in die Übersichtstabelle in Abhängigkeit der Blattnummern in Spalte 1 zugeordnet
- die zugehörigen Zellwerte der jeweiligen Zelle A1 werden korrekt ebenfalls dazugeordnet.
Alles scheint perfekt.

Jedoch:

Sobald ich beide Dateien öffne, geschehen seltsame Datenübernahmen aus der jeweilig anderen Datei wie folgt:
Bin ich in Datei "Test-01" und klicke da Zellen im Tabellenbereich an, dann ist der Dateninhalt aus Datei "Test-01" plötzlich auch in der Übersichtstabelle von Datei "Test-02" aufgeführt und es wird auch die Bedingte Formatierung in Test-02 ausgelöst in Anhängigkeit von angeklickten Zellen in Test-01.
Und dies auch umgekehrt, wenn ich in Test-02 im Tabellenbereich klicke.
Die seltsamen Datenübertragungen verschwinden wieder, wenn ich Taste F9 drücke.

Nun habe ich ausgetestet:

Solange in VBA die Befehlszeile Target.Calculate existiert, dann erfolgen die Datenübernahmen und Auslösung Bedingter Formatierungen gegenseitig jeweils in Abhängigkeit, welche Tabelle gerade aktiv ist und Zelle angeklickt wird. Erst durch drücken der Taste F9 wird der Sollzustand wieder hergestellt.

Wenn ich in VBA meinetwegen nur der Datei Test-01 die Befehlszeile Target.Calculate habe aber nicht mehr in Datei Test-02, dann erfolgt diese seltsame Datenübertragung nur von Datei Test-01 zu Test-02, aber nicht andersherum. Auch wird nur die bedingte Formatierung in Test-02 durch Zellanwahl in Test-01 beeinflusst, nicht aber umgekehrt.

Habe ich in beiden Dateien die Befehlszeile Target.Calculate gar nicht mehr, dann geschehen auch keine gegenseitigen Datenübertragungen mehr, aber eben auch keine bedingter Formatierungen mehr zur Einfärbung aktiver Zeilen.

--------------------------------------

Ich vermute einmal ganz vorsichtig, dass die Benutzung von Target.Calculate zu diesen ominösen ungewollten Effekten führt, dass das gesamte Excelprogramm sich die aktive Zeile der aktiven Datei merkt und meine indirekten Bezüge dazu verleiten, sich dann von dort die Daten zu holen statt von der eigenen Datei.

Was die Indirekten Bezüge anbelangt: Ja, ich weiß, das die volatil sind. Ich habe mich bewusst dafür entschieden, da es für mein Ansinnen die beste Lösung zu sein scheint.
Aber dennoch möchte ich die Einfärbung der aktiven Zeilen beibehalten, nur eben ohne Verursachung der Datenübernahmen von anderen Dateien und ohne von anderen Dateien gesteuert zu werden.

-----------------------------------------

Beide Testdateien hier zum Download:

https://www.herber.de/bbs/user/179668.xlsm
https://www.herber.de/bbs/user/179669.xlsm

Beste Grüße
Danny

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Datenchaos wegen Target.Calculate

20.11.2025 16:32:02

Yal

Hallo Kolja,

beachte, dass bei SelectionChange den "Target" immer die neu ausgewählte Zelle darstellt und nie, die die Du verlässt.

Eventuell kannst Du in dieses Ereignisprozedure auch den Code

Debug.Print Target.Parent.Parent.Name, Target.Parent.Name, Target.Address

reinpasten und damit mehr Übersicht über was passiert.

VG
Yal

AW: Datenchaos wegen Target.Calculate

20.11.2025 21:53:30

schauan

ZELLE("Zeile") ergibt die Zeile der aktiven Zelle in der Application, also excelweit.
Du hast da nicht unterschiedliche Werte für unterschiedliche Dateien, sondern nur den zuletzt berechneten.

AW: Datenchaos wegen Target.Calculate

21.11.2025 22:41:08

Kolja

Naja, mein Problem Nr. 1 ist ja, dass Target.Calculate sich nicht auf jede Datei einzeln bezieht, sondern insgesamt auf die momentan gerade aktive Seite. Diese Info kommt dann bei jeder der geöffneten Dateien an und es wird überall dieselbe Zeile markiert wie in der gerade aktiven Seite.
Das lässt sich schon relativ einfach lösen in Verbindung mit der Regel für die bedingte Formatierung: =ZEILE()=$Z$1
mit dem Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, Me.Range("B6:D23")) Is Nothing Then

        Me.Range("Z1").Value = Target.Row

    End If

End Sub

Aber was mir mehr unter den Nägeln brennt, ist das Herbeischaufeln von Daten aus anderen geöffneten und momentan gerade aktiven Exceldateien.
Ich habe ja im Namensmanager die Formel "Inhalt" generiert und mit der Formel: =ARBEITSMAPPE.ZUORDNEN(1+0*JETZT()) belegt.
Das funktioniert ja auch immer wunderbar - es sein denn man hat mehrere Exceldateien offen. Denn das uralte Excel-4-Makro "Arbeitsmappe.Zuordnen()" ist genauso bescheuert und schaut nur, welche Arbeitsmappe momentan gerade aktiv ist und listet dann deren Blätter auf. Somit sieht man bei zwei geöffneten gleichartigen Exceldateien wie den meinigen im Übersichtsblatt aller offenen Dateien dann immer nur die Auflistung der aktiven Arbeitsmappe. Wenn dann an diesen Blattnamen noch weiterführende Bezüge hängen, die Daten hebeiholen, wird das Ganze richtig blöd. Man kann dann keine optischen Gegenüberstellungen zwischen den Dateien machen, weil alle dieselben Blätter der gerade aktiven Datei anzeigen. Es ist zum Haare ausraufen.

Ich habe mir schon die Finger wund getippt und alles mögliche in VBA probiert, um mir beispielsweise mit folgendem Code für die benannte Funktion "Inhalt":

Public Function Blattnamenliste() As Variant

    Application.Volatile

    

    Dim blattListe() As String

    Dim i As Integer

    Dim wb As Workbook

    

    Set wb = ThisWorkbook

    ReDim blattListe(1 To wb.Sheets.Count)

    For i = 1 To wb.Sheets.Count

        blattListe(i) = "[" & wb.Name & "]" & wb.Sheets(i).Name

    Next i

    Blattnamenliste = blattListe

End Function

Benannte Formel: Inhalt =Blattnamenliste()

Nun klappte das ja erstmal: Die Namen wurden ausgelesen und waren nur noch auf die jeweilige Datei bezogen.
Letzten Endes schaffte ich mir damit aber zusehends mehr Probleme, als mir lieb war.
Der vorgenannte Code Blattnamenliste rechnet bei jedem Pups die Liste neu. Soweit so gut. Das Problem kam damit einher, dass meine Hyperlinks nicht mehr funktionierten:

In Zelle B7 steht eine Blattnummer als Zahl
In Zelle C7 habe meinen Hyperlink gebastelt, der eigentlich vorher mit ARBEITSMAPPE.ZUORDNEN(1+0*JETZT()) wunderbar funktionierte:

=WENN(B7>ANZAHL2(Inhalt);"- - -";HYPERLINK("#'"&INDEX(Inhalt;B7)&"'!A1";TEIL(INDEX(Inhalt;B7);FINDEN("]";INDEX(Inhalt;B7))+1;31)))

Das Problem ist, dass jetzt durch Anklicken des Hyperlinks auch schon die Blattnamen-Ermittlung schon wieder neu angestoßen wurde, bevor der Hyperlink überhaupt loslegen konnte. Und als er loslegenwollte, stand der "neu" zu ermittelnde Blattnamen wohl noch nicht fertig ermittelt bereit, weshalb der Hyperlink nun nicht mehr feststellen konnte, wohin er springen sollte.
Ich habe dann vieles versucht. Durch die ständige Ermittlung der aktiven Zeilennummer in Z1 gibt man ja eh schon dauernd mit jedem Klick den Anstoß zu einer neuen Blattnamenermittlung - und das raubt dann bei etwa 350 Blättern schon so einiges an Performance, und das ständig.

Also wollte ich die Blattnamenermittlung nur an bestimmte Ereignisse/Auslöser knüpfen, die in logischem Zusammenhang mit der kompletten Liste aller Blattnamen stehen und diese Blattnamenliste in irgendeiner Form beeinflussen:
1.) Es wird mittels dem "+"-Symbol unten in der Oberfläche ein neues Blatt eingefügt
2.) Es wird durch Kopieren eines vorhandenen Blattes ein neues Blatt eingefügt
3.) Es wird durch Verschieben aus einer anderen Arbeitsmappe heraus ein neues Blatt oder auch mehrere Blätter zugleich eingefügt
4.) Es wird ein vorhandenes Blatt umbenannt mit neuem Namen
5.) Es wird ein vorhandenes Blatt in der Reihenfolge verschoben
6.) Es wird ein Blatt gelöscht
7.) Es wird durch Verschieben in eine andere Arbeitsmappe hinein ein Blatt oder mehrere Blätter zugleich hinausverschoben / entfernt
8.) Als Rettung/Refresh vielleicht noch explizit durch drücken der Taste F9
Ansonsten ist ja die die Blattnamenliste hinsichtlich Anzahl, Reihenfolge und Namen eigentlich festgenagelt, bis auf die genannten 8 Ereignisse.

Aber das habe ich irgendwie nicht hinbekommen. Die Sache wurde immer verzwickter. Die Hyperlinks liefen immer noch nicht so recht, ich weiß nicht, woran das liegt. Auch wurde fix mal das zwingend nötige Copy&Paste unmöglich, weil meine Routinen dauernd dazwischenfunkten.
Ich kann die Vielzahl der durchprobierten Routinen hier kaum alle darlegen, auch weil sich hier viele Profis wohl ins Fäustchen kichern, was ich hier so zusammenpfriemele.

Vielleicht Kann mich ja jemand von euch auf den richtigen Weg bringen.

Beste Grüße
Danny

AW: hast Du Dir genau überlegt ....

22.11.2025 09:21:58

schauan

... Application.Volatile einzusetzen? Ich wüsste nicht, was das bei der Ermittlung einer Blattnamensliste zu suchen hat ... Die Anzahl der Einträge ändert sich doch nur beim Einfügen oder Löschen eines Blattes, die Einträge ggf. beim Umbenennen

Warum tust Du Deine Blattliste eigentlich immer erneuern? Es gibt da ein zwei hilfreiche Ereignisse, NewSheet und SheetBeforeDelete ...
SheetActivate und SheetDeactivate passt auch, wenn Du das zwischendurch mal prüfen willst.
Nur für das Umbenennen gäbe es (D)ein Problemchen. Ebenso für das Verschieben des aktiven Blattes innerhalb der Arbeitsmappe, da wird nicht neu "sortiert". Wofür Du das so sortiert brauchst - keine Ahnung ...

Wenn Du schon mit VBA arbeitest, kannst Du die Markierung übrigens auch damit setzen statt mit einer bF. Das geht sowohl mit Zellfärbung als auch mit Objekten - siehe da:
https://www.clever-excel-forum.de/Thread-Farbliche-Hervorhebung-der-aktiven-Zeile?pid=161225#pid161225

AW: Datenchaos wegen Target.Calculate

20.11.2025 22:00:55

Onur

Ich weiss sowieso nicht, warum er bedForm UND Makro benutzt.
Für sein Vorhaben reicht doch das Selection_Change-Event völlig aus.

@Onur

21.11.2025 20:20:23

RPP63

Moin Onur!
Der Vorteil von Ereignismakro und bedingter Formatierung liegt imo eindeutig in der problemlosen "Wiederherstellung" der ursprünglichen Zell-Formatierung.
Schaue Dir doch mal meine Datei an.
https://www.herber.de/bbs/user/179673.xlsm

Gruß Ralf

AW: @Onur

21.11.2025 20:54:47

Onur

Hi Ralf,

Stimmt auch nun wieder, an die ursprüngliche Formatierung hatte ich nicht gedacht. Bei einer Zelle ginge es ja noch, aber bei mehreren Zellen wird es komplizierter.
Aber ich traue der "ZELLE"-Funktion nicht, da immer neu berechnet werden muss.
Was hältst du von dieser Lösung ohne Calculate ?

https://www.herber.de/bbs/user/179674.xlsm

Gruß
Onur

AW: @Onur

21.11.2025 21:03:34

RPP63

Was ich davon halte?
Viel!
Wenn man es auf die Spitze treibt, könnte man den Wert von Target.Row und Target.Column auch in zwei Namen schreiben und bräuchte keine "wertvollen" Zellen dafür. ;)

"Zufällig" habe ich dazu auch etwas in petto.
https://www.herber.de/bbs/user/179675.xlsm

Gruß Ralf

AW: Datenchaos wegen Target.Calculate

20.11.2025 17:38:06

Onur

Warum hat die eine Datei Berechnungen auf automatisch stehen und die andere nicht ?

Datenchaos wegen Target.Calculate

Forumthread: Datenchaos wegen Target.Calculate

10
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Datenchaos wegen Target.Calculate

Forumthread: Datenchaos wegen Target.Calculate

10 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

10
Beiträge zum Forumthread

Beiträge zu diesem Forumthread