Office: (Office 365) Problem mit Private Sub Worksheet_Change

Hallo,
ich komme nicht weiter mit meiner Worksheet_Change. Ich muss sagen, ich bin kein VB Profi - eher Laie mit Grundverständnis in Coding.

Was möchte ich (Erwartung):
1. Wenn eine gewisse Zelle "F3" leer ist, dann soll die Formel : =XVERWEIS(VERWEIS($A$1;'Kalkulation(intern)'!$A$4:$C$56)-4;'Allgemeine Daten'!$C$3:$C$68;'Kalkulation(intern)'!$I$3:$I$68;""Fehlt"") angewendet werden.
2. Der Anwender kann aber die Zelle F3 manuell überschreiben mit "wasauchimmer"
3. Wenn der Anwender die Zelle F3 wieder leer (löscht) dann soll somit VB wieder die Formel verwenden.

Aktuelles Coding:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Me.Range("F3") ' Die Zielzelle F3

' Überprüfen, ob die geänderte Zelle die Zielzelle ist
If Not Intersect(Target, rng) Is Nothing Then
Application.EnableEvents = False ' Ereignisse vorübergehend deaktivieren

' Überprüfen, ob die Zielzelle leer ist
If rng.Value = "" Then
' Die Formel in die Zielzelle einfügen
rng.Formula = "=XVERWEIS(VERWEIS($A$1;'Kalkulation(intern)'!$A$4:$C$56)-4;'Allgemeine Daten'!$C$3:$C$68;'Kalkulation(intern)'!$I$3:$I$68;""Fehlt"")"
End If

Application.EnableEvents = True ' Ereignisse wieder aktivieren
End If
End Sub

Fehlerverhalten:
- bei der unter Erwartung 1 geschilderten Formel erhalte ich den Laufzeitfehler 1004 Fehler der Methode Formula des Objekts Range
- Trage ich anstelle meiner komplexeren Formel eine Simple Formel ein, z.b. =SUMME(3+4) dann erhalte ich in der Tabelle meine Formel, aber leider fehlerhaft, in der Zelle F3 erschein nach dem leeren =@SUMME(3+4). Wo kommt denn nun diese @ Zeichen im Formeleditor her? In der Zelle werden mir nur #NAME? angezeigt.
- Wenn ich noch simpler werde und als rng.Formula = "=Q6" eintrage, dann funktioniert alles wie gewünscht. In Q6 steht einfach nur ein Mustertext "text" und der wird mir angezeigt, und im Formeleditor steht =Q6 drin.

 

Hallo Clamsy,

warum willst du mit einem Makro Formeln in ein Tabellenblatt schreiben? Die Berechnungen kannst du doch gleich mit VBA durchführen.

 

Sorry, aber ich verstehe deine Frage nicht. Wie gesagt ich bin kein VBA Profi. Ich will ja eigentlich gar nichts berechnen.

Aktuell macht die XVERWEIS Formel eines, diese Formel vergleicht innerhalb der Zelle F3 ob das Datum einer gewählten Kalenderwoche (in diesem Fall der Zelle F3 der Montag -4, dabei nutzt er Kalkulation intern) ein Arbeitsfreier Tag ist, welcher in der Tabelle Allgemeine Daten vom Anwender Definiert wurde.

Die ganze Formel soll dann noch in einem weiteren Schritt um ein oder zwei Wenn Abfragen erweitert werden (siehe in der XVERWEIS Formel mein Platzhalter "Fehlt". Aber so weit bin ich noch nicht dass ich das einbauen könnte.

VBA ist für mich zwar nicht ganz neu, aber hier fehlen mir fundamentale Grundinformationen. Bis dato habe ich nur selbst darin etwas herumgewurschtelt und keinerlei Kurse zu VBA belegt. Sollte ich mal machen, aber dafür brauche ich das VBA viel viel zu wenig, dass sich das lohnen würde.

 

Moin!
Wenn Du eine deutsche Formel eintragen willst, musst Du die .FormulaLocal-Eigenschaft setzen.

Gruß Ralf

 

Zu spät zum ergänzen, daher neuer Beitrag:
Warum setzt Du nicht einfach einen Blattschutz?

 

Danke für den Hinweis mit local. Werde ich ausprobieren, bin jetzt nur auf Geschäftsreise und komme nicht an meinen privaten pc.

auf deine Frage, warum kein Blattschutz? Könnte ich machen, aber dann wäre die Zelle ja nicht überschreibbar was ja gewünscht ist.

aber das mit local probiere ich.

 

Hallo Clamsy,

Die Formel: =XVERWEIS(VERWEIS($A$1;'Kalkulation(intern)'!$A$4:$C$56)-4;'Allgemeine Daten'!$C$3:$C$68;'Kalkulation(intern)'!$I$3:$I$68;"Fehlt") in die Zelle F3 geschrieben und führt dort eine Aktion durch, wenn diese vorher keinen Inhalt hat. Wenn der Nutzer die Formel mit irgend einem Inhalt überschreibt, passiert nichts. Ist das gewollt? Wenn nicht würde es reichen, einen Blattschutz zu installieren, wie es Ralf ja schon vorgeschlagen hat. Denn Formel oder irgend etwas in der Zelle F3, ist für mich nicht nachvollziehbar. Wie auch immer, die Aktion(en) der Formel könnte man auch per VBA durchführen und nur das Ergebnis in die Zelle F3 schreiben, das war mein Ansatz unter #2.

 

Hallo Klaus-Dieter,

ja das ist genau so gewollt. Ein Blattschutz würde die Zelle schützen, dann kann man aber manuell nichts eintragen. Daher scheidet der Blattschutz aus.
Wenn manuell nichts in diese Zelle eingetragen wird, dann soll per "default" die Formel gelten. Wenn der Anwender auch nur ein X oder J/N oder irgendwas einträgt in diese Zelle, dann soll die Formel nicht gelten, was ja dann auch nicht mehr geht, weil der Anwender die in dieser Zelle hinterlegte Formel überschrieben hat mit seiner manuellen Eingabe. Löscht aber der Anwender den Inhalt seiner manuellen Eingabe, also das X oder J/N oder irgendwas was vorher drin gestanden hat, dann ist ja der Zelleninhalt = "" und wenn der Zelleninhalt ="" ist, dann soll Excel hier per VBA die eigentliche Formel wieder eintragen.

Genau diesen Effekt wünscht man sich.

Ich bin jetzt nach Hause gekommen, ich werde heute Abend mal den Hinweis von RPP_63 anschauen und probieren.

 

Hallo @RPP63_neu ,
vielen Dank für die Tipp mit der Deutschen Formel und der Englischen Variante. Komischerweise hat das mit .FormulaLocal irgendwie nicht funktioniert. Warum auch immer.
Ich habe jetzt in meiner Formel XVERWEIS zu XLOOKUP und VERWEIS zu LOOKUP geändert und es funktioniert jetzt.

Super. Vielen lieben Dank für den Hinweis... Dann kann ich jetzt weiter machen

 

Hi,

ein Tipp für die Zukunft: Wenn du nicht weißt, wie die Formel in VBAisch lautet: Trage sie einfach händisch in die Zelle ein und lese sie dann im Direkt-Fenster mit ?Range("F3").FormulaLocal oder einer anderen .Formulaxxx aus. Dann weißt du gleich, was du angeben musst. Du musst nur "-Zeichen drum herum setzen und berücksichtigen, dass eventuelle "-Zeichen inerhalb der Formel verdoppelt werden müssen.