Office: (Office 2013) VBA: Worksheet_Calculate klappt nicht - soll nur bei einem bestimmten Range starten

Hallo,

ich versuche eine Worksheet_Calculate Funktion zum Laufen zu bringen, aber leider klappt es nicht.
Mein Ziel ist es ein Macro laufen zu lassen wenn in einem bestimmten Range auf einem bestimmten Worksheet sich ein/oder mehrere Werte durch eine Formel ändern. Aktuell wird das Macro bei jeder einzelnen Berechnung der Arbeitsmappe auch wenn auf einem anderen Worksheet diese durchgeführt wird gestartet und das ist nicht gewünscht.

z.B.
Wenn sich auf dem Tabellenblatt „Test“ der Bereich A1:A2 durch die hinterlegten Formel ändert, soll das Macro ausgeführt werden.

Meine bisherigen Versuche enden entweder bei 1) das bei jeder neuen Formelberechnung egal auf was für einen Arbeitsblatt die Berechnung diese gemacht wird oder 2) schon in der „Private Sub Worksheet_Calculate(ByVal Target As Range)“ eine Fehlermeldung kommt.

In Google finde ich verschiedene Posts zu dem Thema aber es klappt einfach nicht.
Auch finde ich zwei verschiedene Herangehensweisen die ich aber nicht ganz verstehe. Was ist der Unterschied zwischen:

1. Private Sub Worksheet_Calculate(ByVal Target As Range)
2. Private Sub Worksheet_Calculate()

Habt Ihr eine Idee wir man soetwas macht?

SG
Tauchmann

zu 1)

Code:

Private Sub Worksheet_Calculate()
    Dim target As Range
    Set target = Worksheets("test1").Range("A1:A2")
    If Not Application.Intersect(target, Range("A1:A2")) Is Nothing Then
        MsgBox "test"
    End If
    Set target = Nothing
End Sub

zu 2)

Code:

Private Sub Worksheet_Calculate(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:A2")) Is Nothing Then
        MsgBox "Hallo"
    End If
End Sub

 

Hallo Tauchmann,

probier mal ob Du damit zurecht kommst:

Code:

Option Explicit

Private Sub Worksheet_Calculate()

Dim rgTarget As Range
    
    With Me
        Set rgTarget = .Range("A1:A2")
        If Not Application.Intersect(rgTarget, .Range("A1:A2")) Is Nothing Then
            MsgBox "test"
        End If
    End With
    
Set rgTarget = Nothing

End Sub

Diese Code muss natürlich in das Modul des Blattes, auf das er sich bezieht.

 

Hallo Achim,

Danke für deine Antwort.
Deinen Code habe ich ausprobiert und behebt erstmal eines der Probleme - nun wird die MsgBox nicht mehr ausgeführt wenn es von einem anderen Worksheet kommt. Leider wird die MsgBox angezeigt wenn auf dem besagten Worksheet eine Formel ausgeführt wird.

Ich habe mal ein Testfile angehangen (ist wirklich sehr rudimentär).

Warum funktioniert das einfach nicht?

SG
Tauchmann

 

Hi,

das Worksheet_Calculate-Ereignis ist das falsche Ereignis für dein Vorhaben, weil es eben bei JEDER Berechnung im Tabellenblatt ausgeführt wird. Benutze stattdessen das Worksheet_Change-Ereignis und überwache die Zellen, aus denen die Formeln in Spalte A ihre Werte beziehen (in deinem Beispiel also Spalte B).

Bis später,
Karin

 

Hallo Karin,

Danke für deine Idee.
In dem richtigen Excelfile gibt es eine Übersichtsseite die aus 5 weiteren Arbeitsblättern gefüttert wird. Die Formeln auf der Übersichtsseite sind in Namen hinterlegt und diese sind wiedrum mit Formeln wie: Bereich.Verschieben etc. ausgeschmückt. Auf der Übersichtsseite wird keiner etwas schreiben können nur auf den weiteren 5 Arbeitsblättern in unzähligen verschiedenen Zellen.

Gibt es da keine Möglichkeit das geschickt zu programmieren?
Ist das wirklich so sonderbar was ich da machen möchte???

SG
Tauchmann

P.S. Danke nochmal für deine Hilfe beim ausrichten der Nulllinie auf der pri. & sek Achse. Klappt wunderbar :)

 

Hi,

logischer Ablauf: auf der Übersichtsseite ändern sich keine Formelinhalte, wenn nicht ein anderes Tabellenblatt aktiv ist und dort etwas geändert wird, ergo: es müssen die anderen Tabellenblätter mit ihrem Change-Ereignis überwacht werden und nicht die Übersichtsseite.

Bis später,
Karin

 

äähm, ja wie denn nun?

 

Hi Achim,

der Fragesteller möchte die MsgBox NUR, wenn sich der Wert einer Formel in einem BESTIMMTEN Bereich des Übersichtsblattes ändert, nicht bei Änderung JEDES Formelergebnisses in diesem Tabellenblatt - denn genau so reagiert (leider) das Calculat-Ereignis.

Bis später,
Karin

 

Huhu Karin,

das hatte ich wohl verstanden, nur anscheinend nicht ordentlich getestet bzw. überlegt.
Dankeschön.
;O)

 

Noch mehr Fragen ?????

Hallo Beverly, Hallo Achim,

sorry für meine späte Antwort.

Danke für Eure Hilfen.
Den Hinweis von Beverly habe ich verstanden, also auf diese Lösung wäre ich nicht gekommen (hier muss ich noch etwas basteln) - werde mich bestimmt dazu mal melden.

@ Achim
Du hast Recht war nicht korrekt von mir (Msgbox und Macro), die Msgbox diente mir nur als Platzhalter wo der Aufruf zu meinem Macro sein soll.

Feedback zur Me Funktion (Bug ???):
Nach dem Test von Achims Vorschlag der Me Funktion in meinem richtigen Workbook hat es dann wieder nicht funktioniert. Nach nun einem längerem Test und rumgebastel in einem Testfile (mit Namensmanager gearbeitet) ist mir aufgefallen das wenn in einem Bereichsnamen mit der Formel Bereich.Verschieben gearbeitet wird, funktioniert die VBA ME Funktion nicht mehr. Entferne ich nun die Bereich.Verschieben Funktion im Namen funktioniert die Me Funktion wieder.

Ich habe mal mein Testfile angehangen.
Es gibt zwei Arbeitsblätter 1 und 2

Mein Bereichsname bezieht sich nur auf 1 und wird als Formel in Blatt 1 verwendet. Blatt 2 hat gar keine Verbindungen zu 1 auch keine Namen die auf 2 verweisen. Wenn man nun in 2 einen Wert eingibt, löst dies das Sub Worksheet_Calculate auf Blatt 1 aus und zeigt mir die Msgbox an.

Könnt Ihr Euch das irgendwie erklären?

SG
Tauchmann