Zellwert kopieren bei Änderung VBA

[diving_excel]

Hallo!
Ich habe eine Frage zu einem VBA Code:

Und zwar habe ich in Zelle D7 eine Summenformel. Wenn sich dieser Wert ändert, soll der unten stehende VBA Code den neuen Wert abgreifen, und in Tabelle 12 fortlaufend untereinander als Wert einfügen. Ziel ist es quasi eine Historie von Werten zu haben.

Das ist der Code den ich bis jetzt hab: 

Code:

Private Sub Worksheet_Calculate()
If Tabelle1.Range("D7").Value <> Zellwert Then
   Tabelle1.Range("D7").Copy
   Tabelle12.Range("A" & (Tabelle12.Cells(Rows.Count, 1).End(xlUp).Row) + 1).PasteSpecial Paste:=xlPasteValues
End If
Zellwert = Tabelle1.Range("D7")
End Sub

Mein Problem ist, dass wenn sich der Wert in D7 ändert, der VBA Code in einer Art Schleife gefangen ist und munter den neuen Wert aus D7 als fortlaufende Liste kopiert.

Habt ihr eine Idee was ich falsch mache? 

Danke und Gruß!

[Elex]

Hi

Versuche es mal damit.

Code:

Private Sub Worksheet_Calculate()
If Range("D7").Value <> Zellwert Then
Sheets("Tabelle12").Range("A" & Sheets("Tabelle12").Cells(Rows.Count, 1).End(xlUp).Row + 1) = Range("D7").Value
End If
Zellwert = Range("D7").Value
End Sub

Gruß Elex

[diving_excel]

Danke, werd ich Montag gleich versuchen!

Gruß

[Ego]

Hallo d...,

zu

.. in einer Art Schleife gefangen ..

das ist eindeutig eine recursive Schleife.

durch eine Kalkulation wird das Ereignismakro aufgerufen,
das eine Aktion durchführt die das Ereignismakro aufruft,
  das eine Aktion durchführt die das Ereignismakro aufruft, 
    das eine Aktion durchführt die das Ereignismakro aufruft,
      das eine Aktion durchführt die das Ereignismakro aufruft, 
        das eine Aktion durchführt die das Ereignismakro aufruft,
          das eine Aktion durchführt die das Ereignismakro aufruft,
            das eine Aktion durchführt die das Ereignismakro aufruft,
              ....
                ....

Wenn man in einem Ereignismakro Aktionen durchführt, die wieder zum Aufruf dieses Makros führen können, sollte man normalerweise zu Beginn des Makros die Ereignisse ausschalten und zum Ende -auch bei einem Abbruch- wieder einschalten.

Code:

Application.EnableEvents = False
....
Application.EnableEvents = True

[diving_excel]

Danke, dass wusste ich noch nicht, was VBA angeht steh ich noch relativ am Anfang.

Ließe sich eine Änderung auch mit einem Datumsstempel versehen? Also in der benachbarten Spalte ein Datum zuordnen? Wenn ich gestern die Excel Datei geöffnet hätte und sich eine Änderung eingestellt hätte, dann das Datum von gestern, wenn ich heute die Excel Datei geöffnet hätte und sich eine Änderung eingestellt hätte, dann das Datum von heute usw...

[diving_excel]

Für andere User falls Interesse besteht, mit diesem Code hat es funktioniert:

Code:

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
If Tabelle1.Range("D7").Value <> Zellwert Then
  Tabelle1.Range("D7").Copy
  Tabelle12.Range("A" & (Tabelle12.Cells(Rows.Count, 1).End(xlUp).Row) + 1).PasteSpecial Paste:=xlPasteValues
End If
Application.EnableEvents = True
Zellwert = Tabelle1.Range("D7")
End Sub

[diving_excel]

Mit diesem Code funktioniert es zwar, aber: 

jetzt kopiert er mir bei jeder Änderung den Zellwert in die dafür vorgesehene Spalte. Also Selbst wenn ich irgendwo eine Überschrift ändere, oder etwas kommentiere, dann läuft dieser VBA Code und setzt einen neuen Eintrag. Was meine Datei erstmal sehr viel langsamer macht, da der VBA Code jetzt bei jeder Aktion ausgeführt wird. Außerdem bekomme ich so eine Menge Mehrfacheinträge...

[RPP63]

Und zwar habe ich in Zelle D7 eine Summenformel. Wenn sich dieser Wert ändert, soll der unten stehende VBA Code den neuen Wert abgreifen, und in Tabelle 12 fortlaufend untereinander als Wert einfügen.

Moin!
Dann solltest Du nicht das Calculate verwenden, sondern die Vorgänger (precedents) der Formel mittels _Change überwachen.
Pseudo-Code:
If Not Intersect(Target, Summenbereich) Is Nothing Then
   'Dein Code
Gruß Ralf

[Elex]

Hi

 den Code in das Modul (Tabelle1).   Dann braucht es auch kein Application.EnableEvents.

Code:

Private Sub Worksheet_Calculate()
Set n = Sheets("Tabelle12").Range("A" & Sheets("Tabelle12").Cells(Rows.Count, 1).End(xlUp).Row)
If Range("D7").Value <> n.Value Then
 n.Offset(1) = Range("D7").Value
 n.Offset(1, 1) = Date
End If
End Sub

Ob der Code im Change besser aufgehoben ist? Das löst auch bei jeder Änderung (Überschrift) erst einmal aus.

Gruß Elex

[diving_excel]

@Elex, dein Code funktioniert bei mir leider nicht, Laufzeitfehler 9; Index außerhalb des gültigen Bereichs.

Lässt sich nicht nur eine Zelle auf Änderung überwachen? Anstatt das ganze Arbeitsblatt? Hab ja schon einen Code der gut funktioniert (sogar etwas zu gut  :19: ), der soll aber nur getriggert werden, wenn sich eine Änderung in Zelle D7 einstellt.