Office: worksheet_change alternative bei Zellen mit Formeln

Hallo zusammen,

Vorneweg:
ich habe eine sehr spezifische Frage, aber leider kein Fachwissen auf dem Gebiet VBA. An den Punkt an dem ich jetzt stehe bin ich mit viel Hilfe und trial&error gelangt. Ausfürliche und einfache Erklärungen wüsste ich sehr zu schätzen. *Smilie

Ich habe mir eine worksheet_change funktion zusammengebastelt bei der ein Feld A1 ausgelesen wird. in diesem Feld A1 können (nur) die Werte 1,2,3 stehen. Bei 1 soll in einem Feld B1 eine Liste mit 2 Auswahlmöglichkeiten kommen. Bei 2 und 3 soll das Feld B1 leer bleiben. So weit so gut.

Nun soll ich das Prizip auf eine Arbeitsmappe anwenden, in der der Wert in A1 (wieder nur 1,2 oder 3) von einer verschachteleten Wenn-Formel errechnet wird, die mit mehreren variablen Schwellenwerten arbeitet.

Die normale worksheet_change funktion funktioniert nur bei manuellen Änderungen in A1.

Meine Frage: Gibt es eine Möglichkeit die worksheet_change funktion zu modifizieren? Oder eine praktikable Alternative?

Vielen Dank für Eure Ideen und beste Grüße

Fabian

:)

 

Hallo Fabian,

da könnte das Ereignis 'Worksheet_Calculate' helfen.

Gruß
Aloys

 

Hallo Aloys,

danke für die schnelle Antwort.

ich stelle mal den Code rein, den ich mir zusammengestümpert habe und der nur funktioniert wenn A1 keine Formel enthält:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address "$A$1" Then Exit Sub
If Range("A1").Value = "1" Then
With Range("B1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Auswahl1,Auswahl2"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Else: Range("B1").Value = ""
Range("B1").Validation.Delete
End If
End Sub

wie würde das mit "Worksheet_Calculate" funktionieren?

Gruß Fabian

 

Hallo Fabian,

wenn möglich, dann überwache die Zellen auf Änderung, die Grundlage für die Formel in A1 sind.

Das Calculate-Ereignis wäre hier nicht mein Favorit.

Grüße
EarlFred

 

Nachtrag: Beispiel
Die Formel in A1 verweist auf C1:C10 (z. B. "=Summe(C1:C10)"):
Code:

Bei externen Bezügen wird das allerdings schlecht handhabbar.

Grüße
EarlFred

 

Hallo EarlFred,

Das Feld A1 bezieht seine Werte aus einem Feld C1 auf dem gleichen Tabellenblatt, welches wiederum seinen Wert aus einem Feld Q1 auf einem 2. Tabellenblatt in der gleichen Arbeitsmappe bezieht, welches seinen Wert aus der Differenz von 2 weiteren Feldern (H1-O1) von Tabellenblatt 2 erhält. Zu guter Letzt wird der Wert von H1 händisch eingetragen und der von O1 ist wieder die Summe aus 2 weiteren Feldern und das ganze aufzudröseln würde noch etwas dauern.

lässt sich mit dem Feld C1 etwas anfangen?

Gruß Fabian

 

Hallo Fabian,

da trifft doch das gleiche zu: Der angezeigte Wert wird per Formel verändert, löst also das Change-Ereignis nicht aus.

Du musst an die Zelle ran, die von Hand geändert wird - nur die löst das Change-Ereignis aus. Aber ehrlicherweise kann ich Deiner Beschreibung dahin nicht folgen.

Grüße
EarlFred

 

Hallo EarlFred,

Wenn ich dich richtig verstanden habe wolltest du einzelne Felder überwachen. Das Problem dabei ist, dass nur in die wenigsten Felder etwas händisch eingetragen wird, die meisten Werte errechnen sich mit Formeln oder sind schlicht statisch.

Danke für deine Hilfe. Die Erkenntnis, dass man in eine Sackgasse läuft ist auch ein Ergebnis.

Gruß Fabian

 

Hallo Fabian,

wer redet von Sackgasse?

Du kannst freilich auch mit dem Calculate-Ereignis arbeiten, dann aber nicht gezielt Zellen überwachen, sondern nur das Blatt komplett - das Makro wird also häufige arbeiten, als Du eigentlich brauchst.

Der Umbau ist auch nicht so schwer:
Code:

dblOldValue soll dafür sorgen, dass das Makro bei der ersten Prüfung abbricht, wenn das Blatt neu berechnet wird und A1 dabei = 1 bleibt.

Den Wert könntest Du ergänzend beim Öffnen des Workbooks erstmalig belegen.

Grüße
EarlFred

 

Jetzt packst du mich aber bei der Neugier.

Habe deinen Code versucht. Kriege eine Fehlermeldung: Laufzeitfehler '13' Typen unverträglich.

nach debuggen ist die Zeile

If Me.Range("E26").Value = "Antrag auf zusätzliche Mobilitätsmittel" And dblOldValue "Antrag auf zusätzliche Mobilitätsmittel" Then

markiert.

und es erscheint kein Dropdown auch beim richtigen Wert.

Gruß Fabian

 

Hallo Fabian,

Du sprachst von Zahlen, daher nahm ich als Typ Double. Wenn die Variable auch Text aufnehmen können soll, nimm Variant.

Grüße
EarlFred

 

Das ist schon mal vielversprechend.

Dropdown erscheint beim richtigen Wert. Kann man es jetzt noch schaffen, dass sowohl Dropdown, als auch der Wert aus dem Dropdown in H26 wieder verschwinden wenn sich der Wert in E26 wieder ändert?

Gruß Fabian

 

Hallo zusammen,

Schamloser selbst bump

Noch mal alles in einem Post:

folgenden Code habe ich dank der tollen Hilfe von EarlFred in meine Arbeitsmappe eingebunden:

Code:

damit erscheint ein Drop down Menu in Zelle H26 wenn der Wert in Zelle E26 sich zu "Antrag auf zusätzliche Mobilitätsmittel" ändert. bei allen anderen Werten in E26 nicht. Das ist schon mal super.
Allerdings bleiben sowohl das Dropdown Menu als auch der letzte Wert, der aus diesem ausgewählt wurde stehen, auch wenn sich der Wert in E26 ändert. Ist es noch möglich die Dropdown List
1.) zu resetten, wenn sich der Wert in E26 ändert?
2.) ganz verschwinden zu lassen, wenn er sich zu etwas anderem als "Antrag auf zusätzliche Mobilitätsmittel" ändert?

Beste Grüße Fabian

 

Hallo Fabian,

fällt Dir das wirklich nicht selbst auf?
Wenn Du die Zellbezüge im einen Codeteil änderst - was muss dann wohl mit den Bezüge im anderen Teil passieren?
Code:

Grüße
EarlFred

 

Hallo EarlFred,

das war wahrlich keine Meisterleistung von mir. Und ich will lieber gar nicht erst sagen wie lange ich vor meinem und deinem neuen Code gesessen habe bevor mir der Unterschied aufgefallen ist...

Ich danke dir für deine Hilfe und Geduld.

Es funktioniert.

Wenn ich den Code jetzt auf mehrere Zellen ausdehnen möchte:
also E26:E29
und H26:H29
aber so, dass E26 weiterhin nur mit H26
und E27 nur mit H27 korrespondiert und so weiter.
kann ich den Code dann einfach Copy Pasten und die Zellbezüge anpassen?